Summary: in this tutorial, you will learn how to sort a result set of a query using SQLite ORDER BY clause.
Introduction to SQLite ORDER BY clause
SQLite stores rows in a table in an unspecified order. It means that the rows in the table may or may not be in the order that they were inserted.
If you use theĀ SELECT statement to query data from a table, the order of rows in the result set is unspecified. To sort the result set, you add the ORDER BY clause in theĀ SELECT statement as follows:
1 2 3 4 5 6 7 | SELECT select_list FROM table ORDER BY column_1 ASC, column_2 DESC; |
The ORDER BY clause comes after the FROM clause. The ORDER BY clause allows you to sort the result set based on one or more columns in different orders: ascending and descending.
In this syntax, you place the column name by which you want to sort after the ORDER BY clause followed by the ASC or DESC keyword.
- The
ASCkeyword means ascending. - And
DESCkeyword means descending.
If you don’t specify the ASC or DESC keyword, SQLite sorts the result set using the ASC option. In other words, it sorts the result set in the ascending order by default.
In case you want to sort the result set by multiple columns, you use a comma (,) to separate columns. The ORDER BY clause sorts rows using columns or expressions from left to right. In other words, the ORDER BY clause sorts the rows using the first column in the list. Then, it sorts the sorted rows using the second column, and so on.
You can sort the result set using a column that does not appear in the select list of the SELECT clause.
SQLite processes NULL value differently. A row with a NULL value is higher than rows with regular values in ascending order, and it is reversed for descending order.
SQLite ORDER BY clause example
Let’s take the tracks table in the sample database for the demonstration.
Suppose, you want to get data from name, milliseconds, and album id columns, you use the following statement:
1 2 3 4 5 6 | SELECT name, milliseconds, albumid FROM tracks; |
The SELECT statement that does not use ORDER BY clause returns a result set that is not in any order.
Suppose you want to sort the result set based on AlbumId column in ascending order, you use the following statement:
1 2 3 4 5 6 7 8 | SELECT name, milliseconds, albumid FROM tracks ORDER BY albumid ASC; |
The result set now is sorted by the AlbumId column in ascending order as shown in the screenshot.
SQLite uses ASC by default so you can omit it in the above statement as follows:
1 2 3 4 5 6 7 8 | SELECT name, milliseconds, albumid FROM tracks ORDER BY albumid; |
Suppose you want to sort the sorted result (by AlbumId) above by the Milliseconds column in descending order. In this case, you need to add the Milliseconds column to the ORDER BY clause as follows:
1 2 3 4 5 6 7 8 9 | SELECT name, milliseconds, albumid FROM tracks ORDER BY albumid ASC, milliseconds DESC; |
SQLite sorts rows by AlbumId column in ascending order first. Then, it sorts the sorted result set by the Milliseconds column in descending order.
If you look at the tracks of the album with AlbumId 1, you find that the order of tracks changes between the two statements.
SQLite ORDER BY with the column position
Instead of specifying the names of columns, you can use the column’s position in the ORDER BY clause.
For example, the following statement sorts the tracks by both albumid (3rd column) and milliseconds (2nd column) in ascending order.
1 2 3 4 5 6 7 8 | SELECT name, milliseconds, albumid FROM tracks ORDER BY 3,2; |
The number 3 and 2 refers to the AlbumId and Milliseconds in the column list that appears in the SELECT clause.
In this tutorial, you have learned how to use the SQLite ORDER BY clause to sort the result set using a single column, multiple columns in ascending and descending orders.