DB2 Group By and Order By Clauses

ORDER BY

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

Group By is to group the results on a column or group of columns.

Notes:

• 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 TOT_STDNT COUNT(*)>50;

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.

Example:
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.