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

User · Answer

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   filter out non-min rows  see above   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   to date  2015-01-01   YYYY-MM-DD   GROUP BY     WORKORDERNUM

User · Answer

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

User · 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

User · Answer

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

User · Answer

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  lt  gt  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

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

If `record_date` has no duplicates within a group:

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

Examples related to sql

Examples related to group-by

Examples related to max

Examples related to distinct

Examples related to min