SQL: Group by minimum value in one field while selecting distinct rows

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 1

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.