How can group functions be used in ORDER BY clause?

MySQLMySQLi Database

We can sort the result set groups by using group functions in the ORDER BY clause. By default, the sort order is ascending but we can reverse it by using DESC keyword.

Example

mysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ) ORDER BY Count(*) DESC;
+-------------+-----------+----------+
| designation | YEAR(Doj) | count(*) |
+-------------+-----------+----------+
| Prof        |      2009 |        2 |
| Asst.Prof   |      2015 |        1 |
| Asst.Prof   |      2016 |        1 |
| Prof        |      2010 |        1 |
| Asso.Prof   |      2013 |        1 |
+-------------+-----------+----------+
5 rows in set (0.00 sec)

mysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ) ORDER BY designation DESC;
+-------------+-----------+----------+
| designation | YEAR(Doj) | count(*) |
+-------------+-----------+----------+
| Prof        |      2009 |        2 |
| Prof        |      2010 |        1 |
| Asst.Prof   |      2015 |        1 |
| Asst.Prof   |      2016 |        1 |
| Asso.Prof   |      2013 |        1 |
+-------------+-----------+----------+
5 rows in set (0.00 sec)
raja
Published on 21-Feb-2018 16:13:20
Advertisements