CODEWITHSUNDEEP
sql
Chris
Chris

Reputation: 7621

SQL - Derived table joining

I have a SQL query for a table in which the user can create multiple 'revisions' of a form. Currently we pass in the ID of the revision into the query to retrieve the values (as you can probably guess), which is fine - however I want to extend to also select the previous revisions rows (if they is a previous revision). Each revision has a number which is incremented when they create a new revision. Here's my query so far which doesn't seem to run (obviously the value 1,value 2 are the actual columns in my query)

SELECT ID, SageJobID, SageJobPK, DateID, Revision, StatusID, Value1, Value2
FROM   CVRs
LEFT OUTER JOIN (SELECT TOP(1) * 
                  FROM CVRs AS prevCVR 
                  WHERE (prevCVR.DateID = CVRs.DateID 
                    AND prevCVR.SageJobPK = CVRs.SageJobPK 
                    AND prevCVR.ID <> CVRs.ID) 
                  ORDER BY prevCVR.Revision DESC) AS 'PrevCVR'
WHERE        (CVRs.ID = @ID)

It seems I can't access the main CVR row I'm selecting from my join. Any ideas?

Upvotes: 1

Views: 2683

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think the easiest way to get multiple most recent revisions is using the ranking functions:

select SageJobID, SageJobPK, DateID, Revision, StatusID, Value1, Value2
from (select CVR.*,
             row_number() over (partition by id order by revision desc) as seqnum
      from CVRs
     ) c
where c.ID = @ID and seqnum <= 2
order by revision desc

Seqnum orders the revisions, by assigning 1 to the most recent, 2 to the second most recent, and so on.

Upvotes: 0

hol
hol

Reputation: 8423

I made an SQL Fiddle with a simplified version to give you an idea how you can solve it. SQL Fiddle Demo here

That would be then about this

SELECT ID, SageJobID, SageJobPK, DateID, Revision, StatusID, Value1, Value2
FROM   CVRs
WHERE  DateId in    ( select dateid    from cvrs where CVRs.ID = @ID)
  and  sagejobpk in ( select sagejobpk from cvrs where CVRs.ID = @ID)
order by revision desc

Edit: I made another SQL Fiddle to also take the DateId aspect in but I got a question: Is it that all CV revisions that belong together must be of same DateId and SageJobPK?

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453573

Looks like you need OUTER APPLY rather than OUTER JOIN

SELECT CVRs.*,
       PrevCVR.* /*TODO: Select desired columns*/
FROM   CVRs
       OUTER APPLY (SELECT TOP(1) *
                    FROM   CVRs AS prevCVR
                    WHERE  ( prevCVR.DateID = CVRs.DateID
                             AND prevCVR.SageJobPK = CVRs.SageJobPK
                             AND prevCVR.ID <> CVRs.ID )
                    ORDER  BY prevCVR.Revision DESC) AS PrevCVR
WHERE  ( CVRs.ID = @ID )

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

SELECT   a.ID, a.SageJobID, a.SageJobPK, a.DateID, a.Revision, a.StatusID, a.Value1, a.Value2
FROM   CVRs as a
LEFT OUTER JOIN  CVRs as b
on .DateID = b.DateID AND a.SageJobPK = b.SageJobPK AND a.ID <> b.ID
where a.ID = @ID  ORDER BY b.Revision DESC

Upvotes: 0

Related Questions