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?

Answer 1

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