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.

Name Age
Mahender 19
Ram 21
Anil 23

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

Name Age
Anil 23
Mahender 19
Ram 21

To sort the result in descending order:

Name Age
Anil 23
Ram 21
Mahender 19

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.

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.

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

Alternatively SAL+INCN can also be used in order by

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:

Groups the students by class they are studying and the number of students in the each class.

Class Tot_stdnt
8 60
9 40
10 59

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.

Derived columns (TOT_STDNT) are not allowed in the having statement.

Class Tot_stdnt
8 60
10 59

Where clause is allowed along with the group by clause and other SQL verbs can also be used.

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.

Class Sex Tot_stdnt
8 F 25
8 M 35
9 F 15
9 M 25
10 F 19
10 M 40

Db2 group by with order by:
Group by with Order by can also be used in the SQL queries.

The above result will be reversed

Class Sex Tot_stdnt
10 M 40
10 F 19
9 M 25
9 F 15
8 M 35
8 F 25

The order by can be used on other columns (derived columns) which are not part of the group by.

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami