How do I order a group by clause in sql

Question

I want to select from my FileList all different files using group by. But I want to get Back only the elements with the highest id. I only get the first entry back. How to fix this?

select * from files group by name;

This is my table-content...

 ID      NAME          DATE               USER 
1443 - /test.txt - 24 May 2013 12:56:15 - XX
1444 - /test.txt - 24 May 2013 12:54:41 - XX
1445 - /test.txt - 24 May 2013 12:53:38 - AB
1446 - /test.txt - 24 May 2013 12:44:32 - XX
1983 - /asdf.txt - 24 May 2013 13:46:32 - KS

That's not how `group by` works. Read this to understand what is going on: http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html — a_horse_with_no_name, May 24 '13 at 21:03

juergen d · Accepted Answer · 2013-05-24T20:35:40.770

0

select * from files 
where id in
(
  select max(id)
  from files
  group by name
)

or

select f.* 
from files f
inner join 
(
  select max(id) as id
  from files
  group by name
) x on x.id = f.id

SQLFiddle demo

edited May 24 '13 at 20:35

answered May 24 '13 at 17:21

juergen d

195,137
36
275
343

1

I don't think this syntax would be valid on an ANSI standard platform.. – Kermit May 24 '13 at 17:22
Really? Why? I can't see the problem. – juergen d May 24 '13 at 17:23
ANSI SQL requires that the select-list include the column(s) in the GROUP BY clause. So you would write the subquery: `(select name, max(id) as id from files group by name)` and that would be okay. – Bill Karwin May 24 '13 at 17:26
Nope, [I'm wrong](http://sqlfiddle.com/#!3/5aa5e/1). For some reason I thought that column in the `GROUP BY` must be in the column list. – Kermit May 24 '13 at 17:27
Yeah, MySQL is more permissive than strict ANSI SQL. MySQL lets you write some forms of ambiguous statements when using GROUP BY, and trusts you know what you're doing. :-) – Bill Karwin May 24 '13 at 17:28
1

@BillKarwin This still shouldn't work in MSSQL. Looks like this is valid. – Kermit May 24 '13 at 17:30
1

Aha - I'm wrong about this. You can have a select-list without the grouping column. Here's a reference on GROUP BY rules based on "SQL-99 Complete, Really": https://kb.askmonty.org/en/rules-for-grouping-columns/ But you can't have a select-list with non-grouping column unless it's inside a grouping function. E.g. `select a, b, max(c) from table group by a` makes b ambiguous. – Bill Karwin May 24 '13 at 18:00

score -1 · Answer 2 · answered May 24 '13 at 17:22

-1

Use this:-

select *,max(id) from files group by name;

answered May 24 '13 at 17:22

Vivek Sadh

4,190
2
29
47

1

I guess it was downvoted because the usage of the group by is incorrect (just like in the original question) - even though it's accepted by MySQL in the standard configuration, see here: http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html – a_horse_with_no_name May 24 '13 at 21:03
Incorrect ? How ?? You mean i should have included 'name' in the select list ? – Vivek Sadh May 25 '13 at 08:00
Yes, exactly. Read the link from my comment. – a_horse_with_no_name May 25 '13 at 08:08

How do I order a group by clause in sql

2 Answers2

SQLFiddle demo