SQLite Order By

SQLite'sORDER BYclause is used to arrange data in ascending or descending order based on one or more columns.

Syntax

The basic syntax of the ORDER BY clause is as follows:

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

• ASCDefault value: ascending order, from small to large
• DESCFrom large to small, descending order

You can use multiple columns in the ORDER BY clause, ensuring that the columns you use for sorting are in the column list:

SELECT
   select_list
FROM
   table
ORDER BY
    column_1 ASC,
    column_2 DESC;

column_1 and column_2, if no sorting rule is specified after them, default to ASC ascending order. The above statement reads by column_1 ascending and column_2 descending, equivalent to the following statement:

SELECT
   select_list
FROM
   table
ORDER BY
    column_1,
    column_2 DESC;

Examples

Assume the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

The following is an example that will sort the results by SALARY in ascending order:

sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

This will produce the following result:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

The following is an example that will sort the results by NAME and SALARY in ascending order:

sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

This will produce the following result:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
5           David       27          Texas       85000.0
7           James       24          Houston     10000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0

The following is an example that will sort the results by NAME in descending order:

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

This will produce the following result:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
1           Paul        32          California  20000.0
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
5           David       27          Texas       85000.0
2           Allen       25          Texas       15000.0