Filtering table by distinct values and multiple rows

Question

Lets say I have a table like this called my_table:

     f_id   name    status  date
 1    12    walnut  passed  7/17/2023
 2    13    beech   passed  7/18/2023
 3    12    walnut  failed  7/19/2023
 4    11    almond  failed  7/20/2023
 5    13    beech   passed  7/21/2023

I know I can get the distinct f_ids and names using:

SELECT DISTINCT(f_id), name, status
FROM my_table

However, what I want it is to get the f_ids and names of the rows where the status = failed but where the status had not previously been 'passed'.

     f_id   name    
 1    11    almond

This will require using the date column, but I am not sure how. Suggestions?

If you downvote, can you at least give a reason? – gwydion93 Jul 21 '23 at 19:00 — gwydion93, Jul 21 '23 at 19:00

score 1 · Accepted Answer · answered Jul 21 '23 at 18:40

You need to join the table on itself but with shift in 1 row. To do it set the number of each row using the window function row_number(). In the row function you can partition by column f_id (like group by) and order windowed rows by date

Try this SQL code:

with cte as 
(
    select 
        f_id,
        status,
        name,
        ROW_NUMBER() over (partition by f_id order by date) row_num
    from my_table
) 
select t1.f_id, t1.name
from cte t1
left join cte t2 on t1.row_num = t2.row_num - 1 
where t1.status = 'failed' and t2.status <> 'passed'

Useful links:

OK, this was really tricky and I like your solution. I didn't consider using the row_number() function; worked great! — gwydion93, Jul 21 '23 at 19:09

Filtering table by distinct values and multiple rows

1 Answers1