Get most recent using datetime

Question

I have following table -

Table	Status	Code	Timestamp
Budget	Success	access	03-02-2024T08:04:23.19Z
Property	Failed	access	03-02-2024T08:04:23.19Z
Transfer	Success	access	03-02-2024T08:04:23.19Z
Property	Success	access	03-02-2024T08:04:23.19Z
Load	Success	access	03-02-2024T00:07:02.54Z
Property	Failed	access	03-02-2024T00:07:02.54Z
Transfer	Success	uprow	03-02-2024T00:07:02.54Z
Load	Success	uprow	01-02-2024T18:17:54.42Z
Property	Failed	uprow	01-02-2024T18:17:54.42Z
Transfer	Success	uprow	01-02-2024T18:17:54.42Z

I want recent timestamp for each of the table, where code is 'access'. This is independent of the status. Please suggest a sql query.

Table	Status	Code	Timestamp
Budget	Success	access	03-02-2024T08:04:23.19Z
Property	Failed	access	03-02-2024T08:04:23.19Z
Transfer	Success	access	03-02-2024T08:04:23.19Z
Load	Success	access	03-02-2024T00:07:02.54Z

Answer 1

This should work for most modern databases:

WITH Numbered AS (
    select "Table", Status, Code, Timestamp
        , row_number() over (partition by "Table" order by Timestamp desc) rn
    from "MyTable"
    where Code = 'access'
)
SELECT "Table", Status, Code, Timestamp
FROM Numbered
WHERE rn = 1
ORDER BY Timestamp DESC

But be careful: some databases still in common use that might seem modern aren't actually up to modern standards, such as Access or MySQL 5.7.

You can see it work here for any supported version of the associated database:

• SQL Server
• Postgres
• MySQL
• SQLite

The only difference between them is the data type for the TimeStamp column and MySQL won't use ANSI object delimiters.

But tell us what database you're using next time, because often the differences are more significant.