Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 485
fearofawhackplanet Avatar asked Jan 31 '11 14:01

fearofawhackplanet


People also ask

How do I get max date records in SQL?

How can I get max date and minimum date in SQL? The SQL MIN() and MAX() Functions The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.

How many maximum Joins we can use in one query?

The maximum number of tables that can be referenced in a single join is 61.

Can we apply join on more than 2 tables?

The join operation is used to combine related rows from two tables into a result set. Join is a binary operation. More than two tables can be combined using multiple join operations.

How many maximum tables can you join?

In Tableau, we can join a maximum of 32 tables.


3 Answers

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.

like image 176
Quassnoi Avatar answered Oct 13 '22 23:10

Quassnoi


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

like image 10
AdaTheDev Avatar answered Oct 13 '22 22:10

AdaTheDev


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)
like image 3
David Avatar answered Oct 13 '22 22:10

David