Use Group Functions in ORDER BY Clause in Oracle

Q

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

✍: 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 employees are having the same salary in each department. The group output is sorted by the count in each group in descending order:

SQL> SELECT department_id, salary, count(*) 
  2  FROM employees GROUP BY department_id, 
  3  salary HAVING count(*) > 1 
  ORDER BY COUNT(*) DESC;
DEPARTMENT_ID     SALARY   COUNT(*)
------------- ---------- ----------
           50       2500          5
           50       3200          4
           50       2800          3
           80      10000          3
           80       9500          3
           50       3100          3
           50       2600          3
.....

 

Understanding SQL SELECT Query Statements in Oracle

⇒⇒Oracle Database Tutorials

2016-10-17, 142👍, 0💬