CODEWITHSUNDEEP
sqlsql-server-2008
sprasai
sprasai

Reputation: 23

Selecting Second row if available if not first in SQL

I have a table that looks like below:

enter image description here

i want to Select Operator entry that corresponds to RowNumber 2 of InstanceNumber 1 and rownumber 1 of other instances. This is one example where only instancenumber 1 has two rownumber's but I want to able to pick last rownumber of each intances. Any help is appreciated.

Upvotes: 2

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method uses window functions:

select t.*
from (select t.*, max(rownumber) over (partition by instancenumber) as maxrn
      from t
     ) t
where rownumber = maxrn;

That said, if you have the right indexes, the following might be a tad faster:

select t.*
from t
where t.rownumber = (select max(t2.rownumber) from t t2 where t2.instancenumber = t.instancenumber);

Upvotes: 2

Related Questions