1

Possible Duplicate:
Fetch the row which has the Max value for a column

My table looks like the following:

Sales 
- id
- country_id
- location_id
- order_id
- created

Now I want only a single row to be returned (the latest, which can be either using the highest id value or using the datetime field created) for each country_id, location_id pair.

Is this possible in a single query?

Community
  • 1
  • 1
Blankman
  • 259,732
  • 324
  • 769
  • 1,199

2 Answers2

1
select * from (
  select * from mytable
  order by id desc) x
group by country_id, location_id;

This is a mysql-only solution, but it works because when you don't aggregate the mon group-by columns, mysql gives you the first row it finds for each group, and if you order the rows before grouping, you get the tow you want.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Won't `GROUP BY` sort by grouping fields, so `order by 1 desc` becomes useless? – meze Nov 25 '12 at 01:13
  • @meze Not useless. It ensures the first row encountered for each combo is the one you want. See edited answer for more explanation – Bohemian Nov 25 '12 at 01:16
  • Right, it's even worse, since there's no guarantee that it will always return the first row for a group. – meze Nov 25 '12 at 01:34
  • @meze that's what a lot of people think, because the doc says that, but in fact it always does return the first one. 100% of time. As reliable as old faithful. I've seen production code run for years relying on it too, and it runs just fine. What can I say? Use and enjoy this handy feature. – Bohemian Nov 25 '12 at 02:35
-1

This should be just

select country_id, location_id, max(created)
from sales
group by country_id, location_id
Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
  • -1 Your query finds the max id for each combo, but he wants the *whole row* with the max id – Bohemian Nov 25 '12 at 01:13
  • Indeed - it is _not_ the case that `country_id` and `location_id` need come from the same row as the `max(created)`! A common misconception. (["The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."](http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html)) – Lightness Races in Orbit Nov 25 '12 at 01:16
  • Erm, lol. I didn't spot that you're grouping on those fields too. Silly me... – Lightness Races in Orbit Nov 25 '12 at 01:18
  • @Bohemian You can't return the *whole row* "for each country_id, location_id pair." Also read the part in braces "the latest, which can be either ... or ...), I've chosen "or" ;-) – Olaf Dietsche Nov 25 '12 at 01:21
  • @OlafDietsche yes you can return the whole row that is the latest for each combo - see my answer for how. My comment stands, because the question clearly wants the whole row, not just the id of the latest row. This is a common requirement that's tricky to do, so we see it a lot here – Bohemian Nov 25 '12 at 05:18
  • @Bohemian Of course you can, but this is an undocumented, implementation dependant mysql-ism. If you aggregate and group by only part of the remaining columns, you cannot return the whole row, as long as you adhere to SQL. Since I don't see this requirement in the question and can't read OP's mind, I just wait, whose answer the OP will accept. Maybe, he even has a third interpretation in mind. Anyway, I agree to disagree ;-) – Olaf Dietsche Nov 25 '12 at 15:58