SQLite Order By

SQLite ORDER BY clause is used to sort the data in either ascending or descending order. The default sort order is ascending. The ORDER BY clause can be used with DESC or ASC keyword to sort the data in descending or ascending order.

Syntax

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

If the ORDER BY clause is omitted, the order of rows returned by the SELECT statement is undefined.

Example

Consider the following table named students:

create table students(
id int, 
name varchar(200), 
grade varchar(10)
);
insert into students(id, name, grade) values
(1, 'Paul', 'C'), (2, 'Allen', 'A'),
(3, 'Tiger', 'B'), (4, 'David', 'A'),
(5, 'Smith', 'C'), (6, 'Jack', NULL);

ID	NAME	GRADE
1	Paul	C
2	Allen	A
3	Tiger	B
4	David	A
5	Smith	C
6	Jack	NULL

Order By DESC

The following statement sorts the result set by grade in descending order:

SELECT * FROM students 
ORDER BY grade DESC;

Order By ASC

The following statement sorts the result set by grade in ascending order (default):

SELECT * FROM students 
ORDER BY grade ASC;

Order By with multiple columns

You can use multiple columns in the ORDER BY clause. The following example sorts the result set first by grade in descending order and then by name in ascending order:

SELECT * FROM students 
ORDER BY grade DESC, name ASC;

Order By with Null Values

If a column contains NULL values and you want to sort the NULL values last, you can use the following statement:

SELECT * FROM students 
ORDER BY grade DESC NULLS LAST;

If a column contains NULL values and you want to sort the NULL values first, you can use the following statement:

SELECT * FROM students 
ORDER BY grade ASC NULLS FIRST;

Order By with LIMIT

You can use the ORDER BY clause in conjunction with the LIMIT clause to construct complex queries. For example, the following query sorts the result set by grade in descending order and then returns only the first two rows:

SELECT * FROM students 
ORDER BY grade DESC LIMIT 2;

Order By with RANDOM

If you want to sort the data in a random order, you can use the RANDOM() function. For example:

SELECT * FROM students 
ORDER BY RANDOM();

This statement returns the entire result set in a random order. If you want to return only a few rows in a random order, you can use the LIMIT clause as follows:

SELECT * FROM students 
ORDER BY RANDOM() LIMIT 3;

This statement returns 3 rows in a random order.