mysql group by and sort each group

Question

I have the following table:

ID NAME TIME
1  A    0
2  A    3
3  B    1

I am using the query below which produces:

SELECT * FROM `table` GROUP BY `NAME`

ID NAME TIME
1  A    0
3  B    1

And I want use GROUP BY to generate a result like this (discount sort by the TIME column):

ID NAME TIME
2  A    3
3  B    1

Solution 1

SELECT NAME, MAX(TIME) as TIME 
FROM table 
GROUP BY time 
ORDER BY time DESC

Solution 2

 select * from (select * from table order by TIME DESC) t group by NAME

Solution 3

Try this solution from here http://www.cafewebmaster.com/mysql-order-sort-group, it was able to solve my problem too :)

Sample:

SELECT * FROM 

(
select * from `my_table` order by timestamp desc
) as my_table_tmp

group by catid

order by nid desc

Solution 4

To get rows with highest time per group you could use a self join

select a.*
from demo a 
left join demo b on a.NAME =b.NAME and a.TIME < b.TIME
where b.NAME is null;

OR

select a.*
from demo a 
join (
  select NAME, max(`TIME`) as `TIME`
  from demo
  group by NAME
) b on a.NAME =b.NAME and a.TIME = b.TIME;

Demo

View more solutions

Comments

[mehov]

This actually worked for me. It gets me less info than the subquery-based solutions, but it's faster.

[Rob Forrest]

There is no assurance that the NAME column will be from the same row as MAX(TIME). It may well work given the above data or a small dataset but is by no means reliable. For a thorough discussion see stackoverflow.com/questions/14770671/…

[JasonMing]

It's work! Seems only ORDER BY clause can accept the alias in SELECT clause, nice trick.

[Peter]

Agree Rob Forrest's comment. You may get bug by this query depend on your use case.

[mkoziol]

It will not work proper in my opinion, grupuping will be always the same, with and without DESC