CODEWITHSUNDEEP
sqljoinleft-join
Kiril
Kiril

Reputation: 40345

SQL query for maximum date

I asked a related question and I got some really good answers, but I'm trying to make the query without making a temporary table. I believe it can be done, but I can't figure out how to do it.

I have two tables and I want to get the latest status for each entry, where the status date is less than or equal to the entry date.

Entries Table (Entry):

  UserID   | EntryDate  
 ----------------------
    1      |  5/4/2010  
    1      |  4/4/2010  
    1      |  3/4/2010  
    1      |  2/4/2010  
    2      |  5/4/2010
    2      |  4/4/2010

Entry Status Table (Status): 

  UserID   | StatusDate  | Detail
  -------------------------------
    1      |  5/28/2010  |  D-1
    1      |  4/24/2010  |  D-2
    1      |  4/5/2010   |  D-3
    1      |  2/28/2010  |  D-4

Expected Output:

UserID | EntryDate | Detail
---------------------------
  1    | 5/4/2010  |  D-2
  1    | 4/4/2010  |  D-4
  1    | 3/4/2010  |  D-4
  1    | 2/4/2010  | <NULL>

In my previous question you can see my effort and the related answers, but I've narrowed down the issue in order to simplify the problem.

I tried to do something like this (but I know I must be horribly off):

         SELECT E.EntryDate, 
                S.Detail, 
                MAX(S.StatusDate) AS MaxStatusDate
           FROM Entry AS E 
LEFT OUTER JOIN Status AS S ON E.EntryDate >= S.StatusDate

Upvotes: 2

Views: 545

Answers (4)

Steve Jorgensen
Steve Jorgensen

Reputation: 12341

I don't think there's a universal way to do it, but in some SQL dialects, you can use TOP or LIMIT in a subquery to get the value from a column in the first matching record, based on a sort (ORDER BY) in another column.

For instance...

SELECT
  Entry.*,
  ( SELECT TOP 1 
      Status.Detail
    FROM Status
    WHERE
      Entry.UserID = Status.UserID AND
      Entry.EntryDate >= Status.StatusDate
    ORDER BY
      Entry.EntryDate
  ) As StatusDetail

Upvotes: 1

Andriy M
Andriy M

Reputation: 77657

SELECT
  e.UserID,
  e.EntryDate,
  s.Detail
FROM Entry e
  INNER JOIN (SELECT DISTINCT UserID FROM Status) u
    ON e.UserID = u.UserID
  LEFT JOIN (
    SELECT
      e.UserID,
      e.EntryDate,
      MAX(s.StatusDate) AS StatusDate
    FROM Entry e
      INNER JOIN Status s
        ON e.UserID = s.UserID AND e.EntryDate >= s.StatusDate
    GROUP BY e.UserID, e.EntryDate
  ) sd
    ON e.UserID = sd.UserID AND e.EntryDate = sd.EntryDate
  LEFT JOIN Status s
    ON sd.UserID = s.UserID AND sd.StatusDate = s.StatusDate

The list of distinct users is used to filter out those users in Entry that are not represented in Status.

The resulting set is then joined with a derived table that contains maximum status date values that correspond to the entry dates.

Finally the Status table is joined again, this time to get the detail info.

The last two joins are left joins, so the result set will contain all rows from Entry for every user that is found in Status, and the inner join guarantees that there will be none for all the others.

Upvotes: 1

Andomar
Andomar

Reputation: 238078

The row_number way, for databases that support it:

SELECT  *
FROM    (
        SELECT  ROW_NUMBER() OVER (
                    PARTITION BY e.UserId, e.EntryDate
                    ORDER BY s.StatusDate desc) as rn
        ,       *
        FROM    Entry e
        INNER JOIN    
                Status s
        ON      s.UserID = e.UserID
                AND s.StatusDate <= e.EntryDate
        ) as SubQueryAlias
where   rn = 1

Upvotes: 1

srgerg
srgerg

Reputation: 19329

I think you want something like

SELECT E.UserID
    , E.EntryDate
    , (SELECT TOP 1 Detail
       FROM Status AS S
       WHERE S.UserID = E.UserID
       AND S.StatusDate <= E.EntryDate
       ORDER BY S.StatusDate DESC)
FROM Entry AS E

If your database doesn't support TOP or for performance reasons you would prefer to avoid the ORDER BY you could try something like:

SELECT E.UserID
    , E.EntryDate
    , (SELECT S1.Detail
       FROM Status AS S1
       WHERE S1.UserID = E.UserID
       AND S1.StatusDate = (SELECT MAX(S2.StatusDate)
                            FROM Status AS S2
                            WHERE S2.UserID = E.UserID
                            AND S2.StatusDate <= E.EntryDate))
FROM Entry AS E

Upvotes: 3

Related Questions