How to Select the 3 Most Recent Records with Unique Values in a Specific Column?

How to Select the 3 Most Recent Records with Distinct Values for a Specified Column

Selecting the most recent records for a given table is a common operation in databases. However, it can become more complex when you want to ensure that the values for a specific column are distinct, eliminating duplicates. This task calls for a more nuanced approach.

In the given scenario, a user attempts to select the three most recent records from a table where the values in the otheridentifier column are distinct. The initial attempt, using SELECT * FROM table GROUP BY (otheridentifier) ORDER BY time DESC LIMIT 3, fails to produce the desired result because grouping takes precedence over ordering in the SQL statement.

To overcome this limitation, a more elaborate query is required:

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 functions by performing the following steps:

1. It employs a subquery to obtain the id of the most recent record for each distinct otheridentifier value.
2. The main query then restricts the selection to records with the corresponding id values.
3. Finally, the results are ordered in descending order of time and limited to the top three records.

Using this approach, the user can effectively select the three most recent records with distinct values for the otheridentifier column, yielding the desired result.

The above is the detailed content of How to Select the 3 Most Recent Records with Unique Values in a Specific Column?. For more information, please follow other related articles on the PHP Chinese website!