CODEWITHSUNDEEP
sqldate
Grevak
Grevak

Reputation: 543

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

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

Upvotes: 0

Views: 93

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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

Upvotes: 0

Justin
Justin

Reputation: 9724

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 |

Upvotes: 0

xzilla
xzilla

Reputation: 1161

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.

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

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)

Upvotes: 1

Related Questions