Problem

Write a query to find the most recent backup for each database on a server.
Example input:
Backup table:
id | database | start_time | end_time
1 | db1 | 2020-01-01 12:00:00 | 2020-01-01 12:05:00
2 | db2 | 2020-01-01 12:10:00 | 2020-01-01 12:15:00
3 | db1 | 2020-01-02 12:00:00 | 2020-01-02 12:05:00
4 | db2 | 2020-01-02 12:10:00 | 2020-01-02 12:15:00
Example output:
id | database | start_time | end_time
3 | db1 | 2020-01-02 12:00:00 | 2020-01-02 12:05:00
4 | db2 | 2020-01-02 12:10:00 | 2020-01-02 12:15:00

Solution

SELECT *
FROM backup
WHERE start_time = (SELECT MAX(start_time) FROM backup GROUP BY database)

/*

The solution above is optimal because it uses a subquery to find the most recent start_time for each database.

*/