Try this code:
select id, max(case when seqcnt = -1 then frequency end) [oldfrequency], max(case when seqcnt = 1 then frequency end) [newfrequency] from ( --this subquery will create new column named seqCnt, which will be equal to: -- -1 when this is the first seqno -- 1 if this is the last seqno -- 0 in other cases select *, case when max(seqno) over (partition by id) = seqno then 1 else case when seqno = 0 then -1 else 0 end end [seqCnt] from @x ) as a -- we only want first or last seqno where seqcnt in (-1, 1) group by id
Note, that you have to change
@x to your actual table name.
solved How to query for the following SQL Server table? [closed]