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

Selecting Most Recent Records with Distinct Values: A Revised Approach

The question presented pertains to retrieving the three most recent records from a table based on a time column, with the added condition that these records must have distinct values in another column, referred to as otheridentifier. To achieve this, a multifaceted SQL query is necessary.

Original Query's Shortcomings

The initially attempted query encountered limitations due to the inherent order of operations in SQL. Grouping occurs before ordering, which resulted in incorrect data being returned.

The Revised Query

To address this, a more sophisticated 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

Explanation:

• The subquery retrieves the id of the most recent record for each distinct otheridentifier value. This subquery ensures uniqueness.
• The main query then selects the top three records from the table based on the time column, ensuring that they have unique otheridentifier values.
• Finally, these records are ordered by their time values in descending order and limited to the three most recent.

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