Joining to MAX date record in group

Question

Job
--------
Id
Description


JobStatus
----------
Id
JobId
StatusTypeId
Date

How do I get the current JobStatus for all jobs?

so something like....

SELECT * FROM Job j
INNER JOIN ( /* Select rows with MAX(Date) grouped by JobId */ ) s
    ON j.Id = s.JobId

(I'm sure there are a bunch of similar questions already but I couldn't find anything which exactly does what I need).

Possible duplicate of [T-SQL Subquery Max(Date) and Joins](http://stackoverflow.com/questions/879111/t-sql-subquery-maxdate-and-joins) — KyleMit, Jul 27 '16 at 17:28

Quassnoi · Accepted Answer · 2011-01-31T14:46:26.770

76

In SQL Server 2005+:

SELECT  *
FROM    job j
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    jobstatus js
        WHERE   js.jobid = j.jobid
        ORDER BY
                js.date DESC
        ) js

In SQL Server 2000:

SELECT  *
FROM    job j
LEFT JOIN
        jobstatus js
ON      js.id =
        (
        SELECT  TOP 1 id
        FROM    jobstatus jsi
        WHERE   jsi.jobid = j.jobid
        ORDER BY
                jsi.date DESC
        )

These queries handle possible duplicates on Date correctly.

edited Jan 31 '11 at 14:46

answered Jan 31 '11 at 14:33

Quassnoi

413,100
91
616
614

1

How have I been missing this since 2005! Thanks for the answer :) – nokturnal Oct 19 '16 at 14:25
1

Just blew my mind... Is this standard SQL or specific to SQL Server? – openwonk Oct 24 '18 at 19:11
1

@openwonk: _TOP_ is specific to SQL Server, most other SQL dialects use _LIMIT_ – Quassnoi Oct 24 '18 at 19:27
2

@Quassnoi, I was referring to `OUTER APPLY` – openwonk Oct 25 '18 at 04:54
3

@openwonk: oh sorry, yes, this is called `LATERAL` in the SQL standard. PostgreSQL and Oracle do support it. – Quassnoi Oct 25 '18 at 09:06

score 10 · Answer 2 · answered Jan 31 '11 at 14:29

10

One way is this:

SELECT j.*, s2.StatusTypeId, s2.Date
FROM Job j
    JOIN
    (
        SELECT JobId, MAX(Date) AS LatestStatusDate
        FROM JobStatus 
        GROUP BY JobId
    ) s1 ON j.JobId = s1.JobId
    JOIN JobStatus s2 ON s1.JobId = s2.JobId AND s1.LatestStatusDate = s2.Date

Assuming you won't have 2 rows in JobStatus for the same JobId + Date combination

answered Jan 31 '11 at 14:29

AdaTheDev

142,592
28
206
200

I know I'm not supposed to thank you. But this is really excellent. Essentially what's happening here is that you're trusting that the highest date connected to that job will be matched to the second more important join. – Zei Sep 29 '20 at 02:41

score 5 · Answer 3 · answered May 06 '16 at 12:45

5

Another (not very efficient, but easy to understand) solution for SQL Server 2000:--

SELECT  *
FROM    job j
WHERE   j.date = (SELECT MAX(date) 
                  FROM   job 
                  WHERE  id = j.id)

answered May 06 '16 at 12:45

David

51
1
2

Joining to MAX date record in group

3 Answers3

Linked