CODEWITHSUNDEEP
sql-servert-sql
fearofawhackplanet
fearofawhackplanet

Reputation: 53396

Joining to MAX date record in group

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).

Upvotes: 45

Views: 82169

Answers (3)

David
David

Reputation: 51

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)

Upvotes: 5

Quassnoi
Quassnoi

Reputation: 425391

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.

Upvotes: 78

AdaTheDev
AdaTheDev

Reputation: 147234

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

Upvotes: 10

Related Questions