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.

I could get to your expected result just by doing this in mysql:

 SELECT id, min(record_date), other_cols
FROM mytable
GROUP BY id

Does this work for you?

To get the cheapest product in each category, you use the MIN() function in a correlated subquery as follows:

    SELECT categoryid,
productid,
productName,
unitprice
FROM products a WHERE unitprice = (
SELECT MIN(unitprice)
FROM products b
WHERE b.categoryid = a.categoryid)

The outer query scans all rows in the products table and returns the products that have unit prices match with the lowest price in each category returned by the correlated subquery.

This a old question, but this can useful for someone In my case i can't using a sub query because i have a big query and i need using min() on my result, if i use sub query the db need reexecute my big query. i'm using Mysql

select t.*
from (select m.*, @g := 0
from MyTable m --here i have a big query
order by id, record_date) t
where (1 = case when @g = 0 or @g <> id then 1 else  0 end )
and (@g := id) IS NOT NULL

Basically I ordered the result and then put a variable in order to get only the first record in each group.

This does it simply:

select t2.id,t2.record_date,t2.other_cols
from (select ROW_NUMBER() over(partition by id order by record_date)as rownum,id,record_date,other_cols from MyTable)t2
where t2.rownum = 1

The below query takes the first date for each work order (in a table of showing all status changes):

SELECT
WORKORDERNUM,
MIN(DATE)
FROM
WORKORDERS
WHERE
DATE >= to_date('2015-01-01','YYYY-MM-DD')
GROUP BY
WORKORDERNUM

I would like to add to some of the other answers here, if you don't need the first item but say the second number for example you can use rownumber in a subquery and base your result set off of that.

SELECT * FROM
(
SELECT
ROW_NUM() OVER (PARTITION BY Id ORDER BY record_date, other_cols) as rownum,
*
FROM products P
) INNER
WHERE rownum = 2

This also allows you to order off multiple columns in the subquery which may help if two record_dates have identical values. You can also partition off of multiple columns if needed by delimiting them with a comma

If record_date has no duplicates within a group:

think of it as of filtering. Simpliy get (WHERE) one (MIN(record_date)) row from the current group:

SELECT * FROM t t1 WHERE record_date = (
select MIN(record_date)
from t t2 where t2.group_id = t1.group_id)

If there could be 2+ min record_date within a group:

1. filter out non-min rows (see above)

2. then (AND) pick only one from the 2+ min record_date rows, within the given group_id. E.g. pick the one with the min unique key:

                   AND key_id = (select MIN(key_id)
   from t t3 where t3.record_date = t1.record_date
   and t3.group_id    = t1.group_id)
   

so

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

will select key_ids: #1 and #4

select
department,
min_salary,
(select s1.last_name from staff s1 where s1.salary=s3.min_salary ) lastname
from
(select department, min (salary) min_salary from staff s2 group by s2.department) s3

SELECT p.* FROM tbl p
INNER JOIN(
SELECT t.id, MIN(record_date) AS MinDate
FROM tbl t
GROUP BY t.id
) t ON p.id = t.id AND p.record_date = t.MinDate
GROUP BY p.id

This code eliminates duplicate record_date in case there are same ids with same record_date.
If you want duplicates, remove the last line GROUP BY p.id.