How Can I Find the Three Most Recent Distinct Record IDs Based on a Shared Column?

Which Distinct Record Id's Are the Most Recent?

In a given database table, certain records are interconnected by a shared column. The objective is to identify the most recent three distinct record identifiers associated with this shared column value.

To achieve this, consider the sample table below:

id	time	text	otheridentifier
1	6	apple	4
2	7	orange	4
3	8	banana	3
4	9	pear	3
5	10	grape	2

Initially, an intuitive approach might be to group by the otheridentifier column and subsequently order by descending time. However, this method would not yield the desired outcome:

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

This query would mistakenly include records with id 3 and 1 instead of the correct ids 4 and 2. The reason for this discrepancy lies in the query execution order: grouping takes precedence over ordering.

To obtain the intended results, a more intricate solution 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 modified query utilizes a subquery to select the single id with the latest time value for each distinct otheridentifier value. This optimized query ensures the desired output of distinct record identifiers 5, 4, and 2.

The above is the detailed content of How Can I Find the Three Most Recent Distinct Record IDs Based on a Shared Column?. For more information, please follow other related articles on the PHP Chinese website!