By user2765924

2013-09-10 17:17:56 8 Comments

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


@epox 2020-06-22 10:42:13

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)


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

@Adriaan Stander 2013-09-10 17:25:35

How about something like:

SELECT mt.*     
        SELECT id, MIN(record_date) AS MinDate
        FROM MyTable
        GROUP BY id
    ) t ON = 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.

@user2765924 2013-09-10 17:41:00

Ah, initially I was using an expression to output a date which was causing the 'and' condition on the inner join to not work properly. Changed it to an actual column and it works now (and had to modify some other things as a result), thanks!

@rajat 2016-02-24 10:32:10

this would not work when two records of same id and date are present, will get you multiple rows right?

@Reese De Wind 2018-06-06 14:26:24

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.

        ROW_NUM() OVER (PARTITION BY Id ORDER BY record_date, other_cols) as rownum,
    FROM products P
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

@SRVFan 2018-04-30 23:26:32

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

    DATE >= to_date('2015-01-01','YYYY-MM-DD')

@Math 2013-09-10 17:33:28

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

 SELECT id, min(record_date), other_cols 
  FROM mytable

Does this work for you?

@user2765924 2013-09-10 17:45:09

For whatever reason, this appears to work in the contrived example (!2/f8469/6/0), but in practice I get "Column 'database.table.col_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I was able to get it working with astander's answer anyway, thanks.

@Pedro Braz 2015-10-23 13:54:07

Yeah I'm running into the same issue, I'd like a simple answer like this one on SQL Server

@jeff 2016-11-08 17:21:35

This does it simply:

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

@Luciano Marqueto 2016-05-05 21:03:39

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.

@DIMI 2016-02-24 23:46:52

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

    SELECT categoryid,
    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.

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

35 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

41 Answered Questions

26 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

11 Answered Questions

[SOLVED] How to Join to first row

32 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2852567 View
  • 1949 Score
  • 32 Answer
  • Tags:   sql duplicates

30 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

18 Answered Questions

Sponsored Content