How to Select the 3 Most Recent Distinct Records from a Table?

Selecting Recent Distinct Records

Your query aims to retrieve the three most recent records from a table where the values in the otheridentifier column are distinct. However, the current approach using GROUP BY and LIMIT yields unexpected results.

Let's examine the problem. The GROUP BY operation combines rows with the same otheridentifier. Since ordering occurs after grouping, each group's most recent record is not necessarily selected.

Solution with Subquery

To address this issue, a subquery can be used to identify the most recent record for each distinct otheridentifier. This information is then used in the main query to select the desired records.

The following query achieves this:

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

Explanation:

• The subquery obtains the id of the most recent record for each unique otheridentifier.
• The main query then uses this id to retrieve the corresponding records from the original table.
• The records are ordered by descending time, and the three most recent are limited by LIMIT 3.

Using this approach, the query will return the expected result: ids 5, 4, and 2, representing the three most recent records with distinct otheridentifier values.

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