How to get the row of a row set with the highest date in SQL?

Question

Table 1:
ID : AnotherID : text  : updateDate
---------------------------------------------
1  :     1     : test1 : 2019-08-08 15:14:19
2  :     1     : test2 : 2019-08-08 15:15:46
3  :     2     : hello : 2019-08-08 14:14:32

Table 2:
ID : text : versionDate
---------------------------------
1  : test :  2019-08-08 16:15:32

SQL Query:

SELECT AnotherID, text
FROM Table1
WHERE updateDate<=(SELECT versionDate FROM Table2 WHERE ID=1)

It outputs everything from Table1 but I only want the rows with ID 2 and 3. How can I get the row of the AnotherID 1 rows with the highest date? Only ID 2 and 3 should be output

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Nick, Aug 09 '19 at 05:06
I added MAX(updateDate) and grouped by Another ID now it shows me the text of row 1 with the date of row 2 — Grevak, Aug 09 '19 at 05:13
Why are you matching on the date when you are selecting based on the ID. Your result set matches what you are asking for in the query: Namely, select all records where the `updateDate` is less than or equal to the `versionDate` of the record in `Table2` (which has the greatest date/time. — daShier, Aug 09 '19 at 05:13
@Grevak . . . I suspect that you want the most recent update date on or before the version date. If so, the currently accepted answer is not correct. — Gordon Linoff, Aug 09 '19 at 11:37

score 1 · Accepted Answer · answered Aug 09 '19 at 05:13

You may try this.

SELECT AnotherID, text
FROM Table1 AS T1 
INNER JOIN (
    SELECT ANOTHERID, MAX(UPDATEDDATE) AS UPDATEDDATE 
    FROM TABLE1 GROUP BY ANOTHERID) AS T2 
ON T1.ANOTHERID=T2.ANOTHERID AND T1.UPDATEDDATE = T2.UPDATEDDATE  
WHERE T1.updateDate<=(SELECT versionDate FROM Table2 WHERE ID=1)

score 1 · Answer 2 · answered Aug 09 '19 at 05:19

The important part here is you need to grab the max date for each AnotherID first, and then join that data back to Table1 to grab the text, something like this:

SELECT 
  AnotherID, text
FROM
  Table1
  JOIN (
        SELECT
          AnotherID, max(updateDate) as mud
        FROM
          Table1
        WHERE
          updateDate <= (SELECT versionDate FROM Table2 WHERE ID=1)
        GROUP BY
          AnotherID
  ) highdate ON (Table1.AnotherID = highdate.AnotherID 
                 AND
                 Table1.updateDate = highdate.mud);

Your question is a little vague as to how you want to filter out the rows which are greater than the versionDate, so you may want to move that where clause after the join, but otherwise the above should work.

score 0 · Answer 3 · answered Aug 09 '19 at 05:25

0

Query:

SELECT [AnotherID], [text]
FROM(
SELECT *,
ROW_NUMBER()over(partition by AnotherID order by updateDate desc) as rn
FROM Table1)a
WHERE a.rn = 1

Result:

| AnotherID |  text |
|-----------|-------|
|         1 | test2 |
|         2 | hello |

answered Aug 09 '19 at 05:25

Justin

9,634
6
35
47

score 0 · Answer 4 · answered Aug 09 '19 at 11:36

You seem to want the most recent version before the date in table 2. If so, the correct logic would be:

SELECT t1.*
FROM Table1 t1
WHERE t1.updateDate = (SELECT MAX(tt1.versionDate)
                       FROM Table1 tt1 JOIN
                            Table2 t2
                            ON tt1.updateDate <= t2.versionDate
                       WHERE t2.ID = 1
                      ) ;

How to get the row of a row set with the highest date in SQL?

4 Answers4