CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 4728

Using group by to get most recent record from table

I have a table that shows order numbers, order descriptions along with the date that they were placed.

Sometimes order numbers are repeated. I want to get all unique order numbers with descriptions but only show the most recent in the event of duplicates

orderID     orderInfo       dateAdded
1           cheese          2014-10-01
1           cheese+         2014-10-02
1           cheese&tomato   2014-10-03
2           bacon           2014-10-05
2           bacon           2014-10-04
2           bacon           2014-10-02
3           tuna            2014-09-30

So my query should return:

orderID     orderInfo       dateAdded
1           cheese&tomato   2014-10-03
2           bacon           2014-10-05
3           tuna            2014-09-30

I have tried:

select orderID, orderInfo from orders group by orderID

But this just gives me the first of each orderID, how do I get it to select the most recent?

Upvotes: 0

Views: 43

Answers (3)

geoandri
geoandri

Reputation: 2428

Try using MAX() function

SELECT orderID,orderInfo,MAX(dateAdded) from (
  SELECT orderID,orderInfo,dateAdded from orders   ORDER BY dateAdded DESC) as t
   group by orderID

Upvotes: 0

dognose
dognose

Reputation: 20889

You could simple LEFT JOIN the table with itself on orderID AND add a join condition, t1.dateAdded < t2.dateAdded.

Finally you pick the results, where t2.dateAdded is null, and you have the latest entry per order. (Because then, the result you have currently in t1 is the latest date inside the join group)

SELECT
  *
FROM
  test t1
LEFT JOIN
  test t2
ON
  t1.id = t2.id AND
  t1.dateAdded < t2.dateAdded
WHERE
  ISNULL (t2.dateAdded);

http://sqlfiddle.com/#!2/0629b2/1

But be aware that for huge tables this might be a huge join!

Look behind the scenes: http://sqlfiddle.com/#!2/0629b2/3

As you can see, you need the rows where t2_dateAdded is null. But as you also can see: the result is already larger compared to the table. So in this example you created 6 additional join results - just to throw them away afterwards. (It is no problem for tables upto 100.000 entries I assume. Still running it on a server with 500.000 rows, and query executes in ~ 2 seconds.)

Upvotes: 2

TioDavid
TioDavid

Reputation: 36

As you said, you want the most recent, so you could use:

select * from orders group by orderID order by dateAdded desc

Upvotes: -1

Related Questions