How do I get records before found record with condition?-CodePudding

I have following structure ordered by date

id, object_id, is_good, created_at
1,  1,        false,    2012-09-01         
2,  1,        false,    2012-09-02 
3,  1,        true,     2012-09-03 
4,  1,        false,    2012-09-04 
5,  1,        true,     2012-09-05 
6,  2,        true,     2012-09-06 
7,  2,        true,     2012-09-07 
8,  2,        false,    2012-09-08

I want to select records before first 'true' in column is_good for each object_id So the expected result

id, object_id, is_good, created_at
1,  1,         false,   2012-09-01         
2,  1,         false,   2012-09-02 
3,  1,         true,    2012-09-03 
6,  2,         true,    2012-09-06

CodePudding user response：

first of all, you need to find all min ids for 'true' values for every object_id. See the "with" part.

Once you have them, all is left to do is join your test_table with that result using object_id and add a condition to get all the lines with id smaller or equals to those you've found in the "with" part

with min_ids as (
select min(id) min_id, object_id
  from test_table
 where is_good = 'true'
 group by object_id)

select * 
  from test_table tt
  join min_ids mi
    on mi.object_id = tt.object_id
 where tt.id <= mi.min_id

dbfiddle