Using Group Functions in the ORDER BY Clause in MySQL

Q

Can Group Functions Be Used in the ORDER BY Clause in MySQL?

✍: FYIcenter.com

A

If the query output is aggregated as groups, you can sort the groups by using group functions in the ORDER BY clause. The following statement returns how many links were created in each year in each tag. The group output is sorted by the count in each group in descending order:

mysql> SELECT tag, YEAR(created), COUNT(*) 
   FROM fyi_links GROUP BY tag, YEAR(created) 
   ORDER BY COUNT(*) DESC;
+------+---------------+----------+
| tag  | YEAR(created) | COUNT(*) |
+------+---------------+----------+
| DBA  |          2006 |        2 |
| DEV  |          2006 |        1 |
| SQA  |          2006 |        1 |
| DBA  |          2005 |        1 |
| DEV  |          2004 |        1 |
| SQA  |          2003 |        1 |
+------+---------------+----------+
6 rows in set (0.00 sec)

 

SELECT Query Statements with GROUP BY in MySQL

⇒⇒MySQL Database Tutorials

2016-10-16, 211👍, 0💬