DB2 Group By and Order By Clauses
DB2 Order by clause is used to sort the result table values by the sort Key order. Here, sort key can be the column name or column number.
Results can be sorted either by Ascending (ASC) or by Descending (DESC) order. ASC is the default order by clause. If you do not specify any collating sequence in order by clause it sorts the records in ascending order by default.
SELECT Name, Age from NAME WHERE AGE>18 ORDER BY AGE ASC;
SELECT Name, Age from NAME WHERE AGE>18 ORDER BY AGE;
It is same as above as ASC is the default order by collating sequence.
If it is to be sorted by Name the result looks like below
SELECT Name, Age from NAME WHERE AGE>18 ORDER BY NAME;
To sort the result in descending order:
SELECT Name, Age from NAME WHERE AGE>18 ORDER BY AGE DESC;
Alternatively column numbers can be specified in the order by clause instead of Column names. This method is useful if the results contain derived values.
SELECT Name, Age from NAME WHERE AGE>18 ORDER BY 2;
Here, 2 represents the second column (AGE) in the result table.
Sort can be done on multiple columns and the priority would be on the order specified.
SELECT Name, dept, sal from EMP table Where sal>10000 Order by dept, sal DESC;
Here, results will be sorted on department and within the same department results will be on the order of salary. NULL values will be considered as the highest value in the order.
Derived columns can also be used in the order by clause
SELECT EMP No, Name, SAL+INCN as â€œNet_Salâ€ From SAL order by Net_SAL;
Alternatively SAL+INCN can also be used in order by
SELECT EMP No, Name, SAL+INCN as â€œNet_Salâ€ From SAL order by SAL+INCN ASC;
Group By is to group the results on a column or group of columns.
- Aggregate functions can be included in the select clause with the column names.
- NULL values forms a separate group.
Single Column Group By:
SELECT CLASS, COUNT(*) AS TOT_STDNT FROM STUDENT GROUP BY CLASS;
Groups the students by class they are studying and the number of students in the each class.
To restrict the group results, “HAVING” clause is used along with the group by. It is like the Where clause in the query. for example to retrieve only the class that is having more than 50 students, “HAVING” can be applied on the count.
SELECT CLASS, COUNT(*) AS TOT_STDNT FROM STUDENT GROUP BY CLASS HAVING
Derived columns (TOT_STDNT) are not allowed in the having statement.
Where clause is allowed along with the group by clause and other SQL verbs can also be used.
SELECT CLASS, COUNT (*) AS TOT_STDNT FROM STUDENT WHERE CLASS >10 GROUP BY CLASS HAVING COUNT(*)>50;
The order should be first Where clause followed by group by and Having.
Multi column group by:
We can use group by clause on more than one column. In this case, first it groups the data on the first column and within that group it groups by the second column and so on.
In the above Student table, to know the total students by sex wise we need to use the two columns in group by. First Class and then with that class by Sex.
SELECT CLASS, SEX, COUNT(*) AS TOT_STDNT FROM STUDENT GROUP BY CLASS, SEX;
Db2 group by with order by:
Group by with Order by can also be used in the SQL queries.
SELECT CLASS, SEX, COUNT(*) AS TOT_STDNT FROM STUDENT GROUP BY CLASS, SEX ORDER BY CLASS DESC;
The above result will be reversed
The order by can be used on other columns (derived columns) which are not part of the group by.