How to change default order of Group By clause in mysql

Question

By default data extracted by group by clause is ordered as ascending. How to change it to descending.

Answer 1

Add DESC to the group by clause, e.g. GROUP BY myDate DESC

Answer 2

ORDER BY foo DESC?

Answer 3

The ORDER BY clause should do what you want.

e.g

select item_name, count(id) as num_items
from items
group by item_name
order by num_items desc

However, there was a bug introduced in MySQL 5.0.50 that caused ORDER BY to break when used with GROUP BY for certain kinds of queries, so if you think your query looks right and the ordering is incorrect, check out: http://bugs.mysql.com/bug.php?id=32202

Answer 4

As the MySQL documentation says,

SELECT * FROM foo GROUP BY bar

is equivalent to

SELECT * FROM foo GROUP BY bar ORDER BY bar

Default behaviour can not be changed, but you can use

SELECT * FROM foo GROUP BY bar ORDER BY bar DESC

without experiencing any speed penalties as the sorting will be performed on the grouped field anyway. By the way, when sorting is not important you can get (small) speed-up by using ORDER BY NULL.

Answer 5

You should use the derived tables on your SQL. For example if you want to pick up the most recent row for an specific activity you're attempt to use:

select * from activities group by id_customer order by creation_date

but it doesn't work.

try instead:

SELECT * FROM (select * from activities order by creation_date desc) 
sorted_list group by id_customer