TSO-ISPF JCL COBOL VSAM DB2 CICS Tools Articles Job Portal Forum Quiz Interview Q&A

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.

ProductIDProductNameCategoryPriceQtyOnHandTotalValue
7001MouseAccessories75.00
7002HarddriveAccessories65.00201,300
7003KeyboardAccessories36.00331,118.00
7004RamComponents23.5016376.00
7005HondaBikes1,200
7006PenStationery7.451074.50
7007PendriveAccessories65.00201,300

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:
ProductIDProductNameCategoryPrice
7006PenStationery7.45
7004RamComponents23.50
7003KeyboardAccessories36.00
7002HarddriveAccessories65.00
7007PendriveAccessories65.00
7001MouseAccessories75.00
7005HondaBikes1,200


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:
ProductIDProductNamePrice
7006Pen7.45
7004Ram23.50
7003Keyboard36.00
7002Harddrive65.00
7007Pendrive65.00
7001Mouse75.00
7005Honda1,200


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:
ProductIDPriceProductName
70067.45Pen
700423.50Ram
700336.00Keyboard
700265.00Harddrive
700765.00Pendrive
700175.00Mouse
70051,200Honda


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:
ProductIDProductNamePrice
7005Honda1,200
7001Mouse75.00
7002Harddrive65.00
7007Pendrive65.00
7003Keyboard36.00
7004Ram23.50
7006Pen7.45


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

Are you looking for Job Change? Job Portal