Can Group Functions Be Used in the ORDER BY Clause?
Page 1 of 1
Can Group Functions Be Used in the ORDER BY Clause?
by Micheal Sun Jan 17, 2010 3:08 am
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(*)
FROM employees GROUP BY department_id, 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
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(*)
FROM employees GROUP BY department_id, 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
Micheal- Admin
- Posts : 243
Join date : 2010-01-10 -
Similar topics» How To Count Groups Returned with the GROUP BY Clause?
» Can Group Functions Be Mixed with Non-group Selection Fields?
» How To Use Subqueries in the FROM clause?
» How To Write an Inner Join with the WHERE Clause?
» How To Write a Left Outer Join with the WHERE Clause?
» Can Group Functions Be Mixed with Non-group Selection Fields?
» How To Use Subqueries in the FROM clause?
» How To Write an Inner Join with the WHERE Clause?
» How To Write a Left Outer Join with the WHERE Clause?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
» Accessing tables of other users in SQL
» Grouping commands in SQL
» Operations on Result Sets in SQL
» Subqueries in SQL
» Joining Relations in SQL
» Delete comand in SQL
» Update command in SQL
» Insert command in SQL