CODEWITHSUNDEEP
sqljoin
Axel Dolce
Axel Dolce

Reputation: 11

SQL JOIN each with most recent row

I have these tables:

table document:

id, docnumber, uploaded_at
...
2, 2014567, '2014-05-30'
3, 9518488, '2014-06-02'

table document_history:

document_id, date, status
2, '2014-06-15', 3
3, '2014-06-12', 3
2, '2014-06-10', 2

and I need to get something like this:

document.id, document.docnumber, document_history.date, document_history.status
2, 2014567, '2014-06-15', 3
3, 9518488, '2014-06-12', 3

I need only one row per document.

Upvotes: 0

Views: 42

Answers (2)

Deepshikha
Deepshikha

Reputation: 10274

Write as:

;with cte as
( select D.id,
         D.docnumber,
         DH.date,
         DH.status,
         row_number() over (partition by D.id order by DH.date desc) as rownum 
   from Document D
   join document_history DH on D.id = DH.document_id
 )
 select id,docnumber,date,status from CTE
 where rownum = 1

DEMO

Upvotes: 0

hines
hines

Reputation: 278

If you are looking for the most recent document_history date then you can use a subquery to return the maximum date and then join it on itself to return the status of that date. 

SELECT
    d.id, 
    d.docnumber,
    dh.MaxDate AS Date,
    dh2.status
FROM @Document d
JOIN (SELECT Id, MAX(date) AS MaxDate 
        FROM @DocumentHistory
        GROUP BY Id) AS dh
    ON dh.id = d.id
JOIN @DocumentHistory AS dh2
    ON dh2.id = dh.id
    AND dh2.date = dh.MaxDate

Upvotes: 1

Related Questions