[Solved] How to query for the following SQL Server table? [closed]


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.

2

solved How to query for the following SQL Server table? [closed]