How to Select Distinct Rows Grouped by Minimum Value in a Database Table?

Select unique rows grouped by minimum value

In the field of database operations, we often encounter the task of grouping data by the minimum value of a specific field while selecting unique rows. Let's say you have a table with multiple rows for each unique identifier, for example:

<code>| key_id | id | record_date | other_cols |
|---|---|---|---|
| 1 | 18 | 2011-04-03 | x |
| 2 | 18 | 2012-05-19 | y |
| 3 | 18 | 2012-08-09 | z |
| 4 | 19 | 2009-06-01 | a |
| 5 | 19 | 2011-04-03 | b |
| 6 | 19 | 2011-10-25 | c |
| 7 | 19 | 2012-08-09 | d |</code>

The goal is to retrieve one row from each unique id, representing the row with the smallest record_date.

A common approach is to use a subquery to identify the minimum record_date for each id and then join it back to the original table:

<code>SELECT *
FROM t
WHERE record_date = (SELECT MIN(record_date) FROM t WHERE id = t.id)</code>

However, this method cannot handle duplicate minimum record_date entries with the same id, which will result in multi-row results.

To solve this problem we need a solution that ensures unique rows. One such approach is to use an inner join and a derived table that calculates the minimum record_date for each id:

<code>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>

The inner join guarantees that only the rows with the minimum record_date for each id are selected, effectively producing a table of unique rows grouped by minimum value.

The above is the detailed content of How to Select Distinct Rows Grouped by Minimum Value in a Database Table?. For more information, please follow other related articles on the PHP Chinese website!