mysql group by and sort each group

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

I开发者_Go百科D NAME TIME
2  A    3
3  B    1

---

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

---

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

---

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

---

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

---

Well, you have to decide what you want to see in the ID and the time fields after the group by. As an example I'll select the MAX(ID) and the SUM(time), then order by totaltime desc.

SELECT MAX(id), name, SUM(time) AS totaltime
FROM YourTableName
GROUP BY name
ORDER BY totaltime DESC

Hope this helps.