SQLite - Sorting Results

The SQLite ORDER BY keyword is used to sort the result table in ascending or descending order. By default, ORDER BY keyword sorts the result in ascending order, however it can be specified using ASC keyword. To sort the result in descending order, DESC keyword is used.

Syntax

The syntax for using ORDER BY keyword in SQLite is given below:

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

Example:

Consider a database table called Employee with the following records:

• To fetch the Employee table sorted by Age (ascending order), the query is:

  SELECT * FROM Employee
  ORDER BY Age ASC;
  

  
  

  This will produce the result as shown below:

  ID	Name	City	Age	Salary
  2	Marry	New York	24	2750
  1	John	London	25	3000
  3	Jo	Paris	27	2800
  5	Ramesh	New Delhi	28	3000
  6	Huang	Beijing	28	2800
  4	Kim	Amsterdam	30	3100

• To fetch all fields of the Employee table sorted by Age (ascending order) and EmpID (descending order), the query will be:

  SELECT * FROM Employee
  ORDER BY Age ASC, EmpID DESC;
  

  
  

  This result of the following code will be:

  ID	Name	City	Age	Salary
  2	Marry	New York	24	2750
  1	John	London	25	3000
  3	Jo	Paris	27	2800
  6	Huang	Beijing	28	2800
  5	Ramesh	New Delhi	28	3000
  4	Kim	Amsterdam	30	3100

Using ORDER BY with CASE Statement

To fetch the rows with their own preferred order, the CASE statement can be used with ORDER BY statement as shown below:

• To fetch the Employee table sorted by City (as defined in CASE statement), the query is:

  SELECT * FROM Employee
  ORDER BY (
    CASE City
      WHEN 'New York' THEN 1
      WHEN 'London' THEN 2
      WHEN 'Amsterdam' THEN 3
      WHEN 'Paris' THEN 4
      ELSE 5
    END
  )ASC, Salary DESC;
  

  
  

  This will sort the Employee City as defined in CASE statement and produce the following result:

  ID	Name	City	Age	Salary
  2	Marry	New York	24	2750
  1	John	London	25	3000
  4	Kim	Amsterdam	30	3100
  3	Jo	Paris	27	2800
  5	Ramesh	New Delhi	28	3000
  6	Huang	Beijing	28	2800