Select rows from a table, ignore rows until a field value is encountered

Question

Let's say I have a table that contains information about user savings to a thrift society and the assessment of each deposit type as good or bad based on internal logic. How do I select rows from this table so that all preceding rows to the last good row are skipped per user?

Before

id     | user |type | amount
----------------------------
20     | 98   | good | 40
35     | 98   | bad  | 30
62     | 98   | good | 20
89     | 98   | bad  | 60
93     | 98   | bad  | 10
100    | 99   | good | 20
103    | 99   | good | 22
109    | 99   | good | 220
121    | 99   | bad  | 640
193    | 99   | bad  | 110

I would like to ignore all records for a user until the last good row is encountered, then subsequent rows can be counted. The rows are ordered by increasing ids which are not consecutive.

After

id     | user |type | amount
----------------------------
62     | 98   | good | 20
89     | 98   | bad  | 60
93     | 98   | bad  | 10
100    | 99   | good | 220
121    | 99   | bad  | 640
193    | 99   | bad  | 110

. . If there are no "good" rows, what do you want returned? – Gordon Linoff Jan 05 '20 at 13:23 — Gordon Linoff, Jan 05 '20 at 13:23
No rows are fine if there are no good rows. – Olaseni Jan 05 '20 at 13:40 — Olaseni, Jan 05 '20 at 13:40

score 1 · Answer 1 · answered Jan 05 '20 at 13:20

Join the table to a query that returns the maximum id for each user with type = 'good':

select t.*
from tablename t inner join (
  select user, max(id) id
  from tablename
  where type = 'good'
  group by user
) tt on tt.user = t.user and tt.id <= t.id

See the demo.
Results:

| id  | user | type | amount |
| --- | ---- | ---- | ------ |
| 62  | 98   | good | 20     |
| 89  | 98   | bad  | 60     |
| 93  | 98   | bad  | 10     |
| 109 | 99   | good | 220    |
| 121 | 99   | bad  | 640    |
| 193 | 99   | bad  | 110    |

score 0 · Answer 2 · answered Jan 05 '20 at 13:17

One method uses a correlated subquery:

select t.*
from t
where t.id >= (select max(t2.id)
               from t t2
               where t2.user = t.user and t2.type = 'good'
              );

This should have good performance if you have an index on (user, type, id).

Based on the phrasing of your question, I am interpreting it as requiring at least one good row. If this is not the case, then the following logic can be used:

select t.*
from t
where t.id >= all (select t2.id
                   from t t2
                   where t2.user = t.user and t2.type = 'good'
                  );

You can also use window functions:

select t.*
from (select t.*,
             max(case when type = 'good' then id end) over (partition by user) as max_good_id
      from t
     ) t
where id >= max_good_id;

score 0 · Answer 3 · answered Jan 05 '20 at 19:37

For MariaDB 10.2+, Window Analytic Functions might be used such as

SUM() OVER (PARTITION BY ... ORDER BY ...)

WITH  T2 AS
(
SELECT SUM(CASE WHEN type = 'good' THEN 1 ELSE 0 END) 
           OVER (PARTITION BY user ORDER BY id DESC) AS sum,
       T.*
  FROM T     
)
SELECT id, user, type, amount
  FROM T2
 WHERE ( type = 'good' AND sum = 1 )  OR ( type != 'good' AND sum = 0 )
 ORDER BY id;

Demo

Select rows from a table, ignore rows until a field value is encountered

3 Answers3