Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

ID NAME TIME
2  A    3
3  B    1
like image 270
lachekar Avatar asked Dec 15 '10 09:12

lachekar


People also ask

Can we use GROUP BY and ORDER BY together in MySQL?

Both GROUP BY and ORDER BY are clauses (or statements) that serve similar functions; that is to sort query results. However, each of these serve very different purposes; so different in fact, that they can be employed separately or together.

Does GROUP BY sort data in MySQL?

In MySQL, GROUP BY is used for sorting, so the server may also apply ORDER BY optimizations to grouping.

How do you use GROUP BY and ORDER BY?

It is used to arrange similar data into the group. The GROUP BY clause follows the WHERE clause and comes before the ORDER BY clause.

How do I sort by group in SQL?

The general syntax for selecting values and sorting them is: SELECT column_name, column_name FROM table_name ORDER BY column_name ASC, column_name DESC; Note that ORDER BY() will automatically sort the returned values in ascending order so the use of the ASC keyword is optional.


2 Answers

SELECT NAME, MAX(TIME) as TIME 
FROM table 
GROUP BY time 
ORDER BY time DESC
like image 191
Jiri Kratochvil Avatar answered Oct 25 '22 05:10

Jiri Kratochvil


 select * from (select * from table order by TIME DESC) t group by NAME
like image 29
php Avatar answered Oct 25 '22 05:10

php