How to Select Rows with the Minimum Record_Date for Each Unique ID in a Table?

Retrieving Rows with the Earliest Record Date for Each Unique ID

Working with tables containing duplicate id values and needing to select only the row with the earliest record_date for each unique id can be tricky. Standard methods often struggle when record_date values aren't unique. This solution efficiently handles such scenarios:

The following SQL query effectively selects the desired rows:

<code class="language-sql">SELECT mt.*
FROM MyTable mt
INNER JOIN (
    SELECT id, MIN(record_date) AS MinDate
    FROM MyTable
    GROUP BY id
) t ON mt.id = t.id AND mt.record_date = t.MinDate;</code>

This query uses a subquery to first determine the minimum record_date for each id. The subquery groups the rows by id and then uses the MIN() function to find the minimum record_date within each group.

The outer query then joins the results of the subquery back to the original table (MyTable) using both id and record_date. This ensures that only rows matching the minimum record_date for each id are included in the final result set. This effectively removes duplicates and returns only the rows with the earliest record_date for each unique id.

The above is the detailed content of How to Select Rows with the Minimum Record_Date for Each Unique ID in a Table?. For more information, please follow other related articles on the PHP Chinese website!