Group by minimum value in one field while selecting distinct rows

ghz 1years ago ⋅ 10025 views

Question

Here's what I'm trying to do. Let's say I have this table t:

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

For each id, I want to select the row containing the minimum record_date. So I'd get:

key_id | id | record_date | other_cols
1      | 18 | 2011-04-03  | x
4      | 19 | 2009-06-01  | a

The only solutions I've seen to this problem assume that all record_date entries are distinct, but that is not this case in my data. Using a subquery and an inner join with two conditions would give me duplicate rows for some ids, which I don't want:

key_id | id | record_date | other_cols
1      | 18 | 2011-04-03  | x
5      | 19 | 2011-04-03  | b
4      | 19 | 2009-06-01  | a

Answer

How about something like:

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

This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates is if there are duplicate minimum record_dates for the same ID.

#sql