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

Answer

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)

Advertisement

Answer