SQL ORDER BY || DB2 for i SQL

ORDER BY clause in DB2 for i SQL

The ORDER BY clause is used to sort the result set in ascending or descending order according to one or more column.

By default, ORDER BY sort the data in ascending order.

We can use the keyword DESC to sort the result set in the descending order and the keyword ASC to sort the result set in ascending order.

The column specified in the ORDER BY clause does not need to be included in the SELECT clause.

Null values are ordered as the highest value.

If an AS clause is specified to name a result column in the select-list, this name can be specified in the ORDER BY clause.

Instead of naming the columns to order the results, you can use a number. ORDER BY 2 specifies that you want the results ordered by the second column of the results table, as specified by the select-list.

Syntax of ORDER BY clause

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

Create Table Customer

Use the existing table Customer to understand this clause usage.

Example 1: Sort the table Customer by CustName

SELECT * FROM customer ORDER BY CUSTNAME

Output:

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     1   Amit                  PATNA                 BIHAR                 INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      

Example 2: Sort the table Customer by Highest to lowest CustId

SELECT * FROM customer ORDER BY CUSTID DESC

Output:

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     1   Amit                  PATNA                 BIHAR                 INDIA      

Example 3: Sort the table Customer by CustState and CustCity (ORDER BY more than one columns)

SELECT * FROM customer ORDER BY CUSTSTATE, CUSTCITY

Output:

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     1   Amit                  PATNA                 BIHAR                 INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      

Example 4: Sort the table Customer by CustState and CustCity (ORDER BY more than one columns and using both ASC and DESC)

SELECT * FROM customer ORDER BY CUSTSTATE asc, CUSTCITY desc

Output:

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     1   Amit                  PATNA                 BIHAR                 INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      

Example 5: Sort the table Customer by Column Number instead of Name in Descending order.

SELECT * FROM customer ORDER BY 1 desc

Output:

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     9   Kannan                CHENNAI               TAMIL NADU            INDIA      
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
     7   Mounish               KOLKATA               WEST BENGAL           INDIA      
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
     5   Yatin                 IDUKKI                KERALA                INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     2   Anil                  PATNA                 BIHAR                 INDIA      
     1   Amit                  PATNA                 BIHAR                 INDIA      

Example 6: Sort the table Customer by using the column name specified using AS clause in SELECT list

SELECT CUSTID as Customer_Id , CUSTNAME, CUSTCITY, CUSTSTATE,
CUSTCOUNTRY FROM customer ORDER BY Customer_Id DESC          

Output:

CUSTOMER_ID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
          9   Kannan                CHENNAI               TAMIL NADU            INDIA      
          8   Annamalai             CHENNAI               TAMIL NADU            INDIA      
          7   Mounish               KOLKATA               WEST BENGAL           INDIA      
          6   Tinku                 AGRA                  UTTAR PRADESH         INDIA      
          5   Yatin                 IDUKKI                KERALA                INDIA      
          4   Udeep                 KOCHI                 KERELA                INDIA      
          3   Dhanraj               HISAR                 HARYANA               INDIA      
          2   Anil                  PATNA                 BIHAR                 INDIA      
          1   Amit                  PATNA                 BIHAR                 INDIA      

Example 7: Sort the table Customer by using the not named column in the Select list

SELECT CUSTID , CUSTNAME, CUSTCITY CONCAT CUSTSTATE CONCAT          
CUSTCOUNTRY FROM customer ORDER BY CUSTCITY CONCAT CUSTSTATE CONCAT 
CUSTCOUNTRY                                                         

or, this can also sort it by ORDER BY using column number of the unnamed column in the select list.

SELECT CUSTID , CUSTNAME, CUSTCITY CONCAT CUSTSTATE CONCAT
CUSTCOUNTRY FROM customer ORDER BY 3                      

Output:

CUSTID   CUSTNAME              CONCAT                 
     6   Tinku                 AGRAUTTAR PRADESHINDIA 
     8   Annamalai             CHENNAITAMIL NADUINDIA 
     9   Kannan                CHENNAITAMIL NADUINDIA 
     3   Dhanraj               HISARHARYANAINDIA      
     5   Yatin                 IDUKKIKERALAINDIA      
     4   Udeep                 KOCHIKERELAINDIA       
     7   Mounish               KOLKATAWEST BENGALINDIA
     1   Amit                  PATNABIHARINDIA        
     2   Anil                  PATNABIHARINDIA