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
SELECT NAME, MAX(TIME) as TIME
FROM table
GROUP BY time
ORDER BY time DESC
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/…
ORDER BY clause can accept the alias in SELECT clause, nice trick.
ORDER BY is sorting the final result after GROUP BY. 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;
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.
