SQLite Order By
The ORDER BY clause in SQLite is used to sort data based on one or more columns in ascending or descending order.
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];
- ASC is the default value, sorting from smallest to largest, in ascending order.
- DESC sorts from largest to smallest, in descending order.
You can use multiple columns in the ORDER BY clause, ensuring that the columns used for sorting are listed in the column list:
SELECT
select_list
FROM
table
ORDER BY
column_1 ASC,
column_2 DESC;
If the sorting rules are not specified after column_1 and column_2, they default to ASC (ascending). The above statement sorts by column_1 in ascending order and column_2 in descending order, which is equivalent to the following statement:
SELECT
select_list
FROM
table
ORDER BY
column_1,
column_2 DESC;
Example
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
Here 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
Here 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
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
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
Here is an example that will sort the results in descending order by NAME:
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