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

By reading your question, it is little unclear of how you want the fields to be sorted. — Starx, Dec 15 '10 at 09:43
It's not 100% clear what result exactly you are trying to get. Perhaps try articulating *what* you actually want to do. — Josh, Mar 08 '13 at 03:25

score 28 · Answer 1 · edited Mar 08 '13 at 02:15

28

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

edited Mar 08 '13 at 02:15

Taryn

242,637
56
362
405

answered Dec 15 '10 at 09:41

Jiri Kratochvil

305
2
3

This actually worked for me. It gets me less info than the subquery-based solutions, but it's faster. – ᴍᴇʜᴏᴠ Jun 26 '16 at 11:19
15

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 http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by?noredirect=1#comment63515985_14770671 – Rob Forrest Jun 27 '16 at 07:55
It's work! Seems only `ORDER BY` clause can accept the alias in `SELECT` clause, nice trick. – JasonMing Dec 27 '17 at 07:22
Agree Rob Forrest's comment. You may get bug by this query depend on your use case. – Peter Oct 17 '18 at 20:41
It does not work for big data. `ORDER BY` is sorting the final result after `GROUP BY`. – menoktaokan Aug 28 '23 at 06:31

score 7 · Answer 2 · edited Aug 24 '14 at 19:23

7

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

edited Aug 24 '14 at 19:23

Brad Larson

170,088
45
397
571

answered Jan 08 '14 at 06:31

php

4,307
1
24
13

1

It will not work proper in my opinion, grupuping will be always the same, with and without DESC – mkoziol Mar 24 '19 at 13:08

score 5 · Answer 3 · answered Jul 30 '12 at 15:07

5

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

answered Jul 30 '12 at 15:07

dreamluverz

59
1
1

score 4 · Answer 4 · answered May 16 '18 at 05:11

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

score 0 · Answer 5 · answered Dec 15 '10 at 09:44

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.

mysql group by and sort each group

5 Answers5

Linked