DB2 - SQL Order By Keyword

To explicitly sort data retrieved using a SELECT statement, the ORDER BY clause is used.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Syntax:

SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1, column2, ... ASC|DESC;

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1: Sorting by Single Columns

SELECT ProductID, ProductName, Category, Price
  FROM Product
  ORDER BY Price;

This statement sorts the data alphabetically in the ascending order by the Price column.

Result:

Example 2: Sorting by Multiple Columns

To sort by multiple columns, simply specify the column names separated by commas in the ORDER BY clause.

SELECT ProductID, ProductName, Price
  FROM Product
  ORDER BY Price, ProductName;

This code retrieves three columns and sorts the results by two of them, first by Price and then by ProductName (i.e. If more than one record present with same price. those records will be sorted again based on the name).

Result:

Example 3: Sorting by Column Position

ORDER BY also supports ordering specified by relative column position.

SELECT ProductID, Price, ProductName
  FROM Product
 ORDER BY 2, 3;

ORDER BY 2 means sort by the second column in the SELECT list, the Price column. ORDER BY 2, 3 means sort by Price and then by ProductName.

Result:

Example 4: Specifying Sort Direction

Data sorting is not limited to ascending sort orders (from A to Z). Although this is the default sort order, the ORDER BY clause can also be used to sort in descending order (from Z to A).

To sort by descending order, the keyword DESC must be specified.

SELECT ProductID, ProductName, Price
  FROM Product
ORDER BY Price DESC;

This SQL sorts the products by Price in descending order (most expensive product present first).

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!