CODEWITHSUNDEEP
mysqlsql
Yash
Yash

Reputation: 105

Select Rows based on a unique condition

Input I would like to get all the details of ID's whose prev is 'SC' and if its inserted first for that ID

Table1:

id Prev Next Time_inserted
1 MA BP 9:00
1 BP SC 9:01
2 MA AP 9:02
2 BP MA 10:00
3 SC AP 11:00
3 AP BP 11:01
5 SC AP 12:00

So my Expected output is

id Prev Next Time_inserted
3 SC AP 11:00
3 AP BP 11:01
5 SC AP 12:00

Since For Id's 3 & 5 the first inserted row in Prev is SC

This is what I tried so far,

with cte1 as  (select id
from Table1
where prev= 'SC' ) ,

 data as (select a.*, b.id as uno, row_number() over (partition by a.id, time_inserted ORDER BY a.time_inserted asc) as rn
from Table1  a
left join cte1 b on a.id= b.id
where prev = 'SC' ),

intermediate_D as(
select a.*, a.id
from data a
where rn = 1 )

select *
from intermediate_D a
join Table1 b on a.id = b.id

But the expected output is not achieved

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You can use first_value():

select t.*
from (select t.*,
             first_value(prev) over (partition by id order by time_inserted) as first_prev
      from table1 t
     ) t
where first_prev = 'SC';

Upvotes: 1

Related Questions