SQL Server - Deriving A table

Question

I have the following query which yields the following result.

select RowNumber, ApplicationDecisionID, RowNumber-1 as previousDescIDRowNumber from (
  select ROW_NUMBER() OVER(ORDER BY applicationdecisionid ASC) AS RowNumber, ApplicationDecisionID from dscs_public.ApplicationDecision where ApplicationID in (
    select applicationid from dscs_public.Application where ApplicationID = (
        select ApplicationID from dscs_public.ApplicationDecision
        -- where ApplicationDecisionID = @applicationDescID
        where ApplicationDecisionID = 989
    )
)) t

Yields:

RowNumber            ApplicationDecisionID previousDescIDRowNumber
-------------------- --------------------- -----------------------
1                    705                   0
2                    989                   1

I would like to the previousDescIDRowNumber to actually be the preceding ApplicationDecisionID and not just the row number, like so :

RowNumber            ApplicationDecisionID previousDescIDRowNumber
-------------------- --------------------- -----------------------
1                    705                   <blank>
2                    989                   705

Can anyone help with the query for this please?

Can you post an example of your desired results? I can't make sense of your sentence about what you want. — Tab Alleman, Oct 12 '15 at 13:06
Sure... I'd like this please: RowNumber ApplicationDecisionID previousDescIDRowNumber -------------------- --------------------- ----------------------- 1 705 2 989 705 ack... terrible formatting! basically, in row 2, I'd like 705 to be where 1 is in previousDescIDRowNumber. thanks...... thanks to the reviewer! — dstewart101, Oct 12 '15 at 13:10
Which version of SQL are you using? This looks like a Case for the LAG function. — Brad D, Oct 12 '15 at 13:14

score 1 · Accepted Answer · answered Oct 12 '15 at 13:18

You can left join your query to itself:

.......;
with q as(
    select RowNumber, ApplicationDecisionID 
    from (
        select ROW_NUMBER() OVER(ORDER BY applicationdecisionid ASC) AS RowNumber
        , ApplicationDecisionID 
        from dscs_public.ApplicationDecision 
        where ApplicationID in (
            select applicationid from dscs_public.Application 
            where ApplicationID = (
                select ApplicationID from dscs_public.ApplicationDecision
                where ApplicationDecisionID = 989
            )
        )
    ) t
)
Select q1.RowNumber, q1.ApplicationDecisionID, q2.ApplicationDecisionID     
From q as q1
Left Join q as q2 on q1.RowNumber-1 = q2.RowNumber

SQL Server - Deriving A table

1 Answers1