How to Select the 3 Most Recent Distinct Records in SQL?

Selecting Distinctly Recent Records with SQL

In this query, the task is to extract the three most recent records from a table while ensuring that the values in a specific column, otheridentifier, are distinct. The following SQL statement was attempted:

SELECT * FROM `table` GROUP BY (`otheridentifier`) ORDER BY `time` DESC LIMIT 3

This query, however, yielded unexpected results due to the precedence of grouping over ordering in SQL. To achieve the desired outcome, a more intricate approach is necessary:

SELECT *
FROM `table`
WHERE `id` = (
    SELECT `id`
    FROM `table` as `alt`
    WHERE `alt`.`otheridentifier` = `table`.`otheridentifier`
    ORDER BY `time` DESC
    LIMIT 1
)
ORDER BY `time` DESC
LIMIT 3

This query consists of a nested select statement that identifies the most recent record for each distinct otheridentifier value. The result is then filtered to ensure that only the three most recent records are retrieved.

The above is the detailed content of How to Select the 3 Most Recent Distinct Records in SQL?. For more information, please follow other related articles on the PHP Chinese website!