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.

Answer 2

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?

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

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

Answer 10

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