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

Solution 1:

SELECT NAME, MAX(TIME) asTIMEFROMtableGROUPBYtimeORDERBYtimeDESC

Solution 2:

select*from (select*fromtableorderbyTIMEDESC) t groupby 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` orderby timestamp desc
) as my_table_tmp

groupby catid

orderby 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 isnull;

OR

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

Demo

Solution 5:

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.

SELECTMAX(id), name, SUM(time) AS totaltime
FROM YourTableName
GROUPBY name
ORDERBY totaltime DESC

Hope this helps.