SQLite Tutorial SQLite Advanced SQLite Database SQLite References

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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • 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:

    IDNameCityAgeSalary
    2MarryNew York242750
    1JohnLondon253000
    3JoParis272800
    5RameshNew Delhi283000
    6HuangBeijing282800
    4KimAmsterdam303100
  • 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:

    IDNameCityAgeSalary
    2MarryNew York242750
    1JohnLondon253000
    3JoParis272800
    6HuangBeijing282800
    5RameshNew Delhi283000
    4KimAmsterdam303100

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:

    IDNameCityAgeSalary
    2MarryNew York242750
    1JohnLondon253000
    4KimAmsterdam303100
    3JoParis272800
    5RameshNew Delhi283000
    6HuangBeijing282800