I want to pick the IDs when the two conditions are met

1. Col2 of every ID should have 1 and
2. the consecutive row of same ID should be 2

it does not matter where 1 is but 2 must be the consecutive row after 1.

output

I tried to use this code but here it outputs all the IDs

SELECT *
from            (SELECT IDs, Col2 FROM table_name where Col2 = 1)a
left outer join (SELECT IDs, Col2 FROM table_name where Col2 = 2)b
             on a.IDs=b.IDs

Help is appreciated.

CodePudding user response：

To do what you are asking for you should use window functions. Make it row_number or lead ones.

One option is:

SELECT t0.IDs
from (select *, ROW_NUMBER OVER (PARTITION BY IDs) as rn from table_name) t0
left join (select *, ROW_NUMBER OVER (PARTITION BY IDs) as rn from table_name) t1 on (t0.IDs = t1.IDs AND t0.rn   1 = t1.rn)
where t0.Col2=1 and t1.Col2=2

CodePudding user response：

You can join the table with itself. Each instance picks row #1 and row #2 respectively, and the join ensures both are present.

For example:

select t.ids
from t
join t b on b.ids = t.ids
where t.col2 = 1 and b.col2 = 2