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:

id | record_date | other_cols
18 | 2011-04-03  | x
18 | 2012-05-19  | y
18 | 2012-08-09  | z
19 | 2009-06-01  | a
19 | 2011-04-03  | b
19 | 2011-10-25  | c
19 | 2012-08-09  | d

For each id, I want to select the row containing the minimum record_date. So I'd get:

id | record_date | other_cols
18 | 2011-04-03  | x
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:

id | record_date | other_cols
18 | 2011-04-03  | x
19 | 2011-04-03  | b
19 | 2009-06-01  | a

7 comments

@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.

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

@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):

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

@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
  GROUP BY id

Does this work for you?

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

For whatever reason, this appears to work in the contrived example (sqlfiddle.com/#!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:

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

@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,
       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.

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

How about something like

SELECT mt.*,     
FROM MyTable mt INNER JOIN
    (
        SELECT ID, MIN(Record_Date) 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.

@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?

Related Questions

Sponsored Content

16 Answered Questions

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

39 Answered Questions

33 Answered Questions

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

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2659796 View
  • 1798 Score
  • 29 Answer
  • Tags:   sql duplicates

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

23 Answered Questions

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

10 Answered Questions

[SOLVED] SQL Server: How to Join to first row

17 Answered Questions

Sponsored Content