SQLite | Query Data | Sort Rows by Column Values (ORDER BY)
Without explicit ordering, query results follow no guaranteed order. The ORDER BY clause sorts rows by specified columns. This article explains how to use it.
Sorting with ORDER BY
Use ORDER BY in a SELECT statement to sort by specified columns. The syntax is as follows.
SELECT column_name, ... FROM table_name ORDER BY column_name [ASC|DESC];
After ORDER BY, specify the column and direction: ASC for ascending or DESC for descending. Omitting the direction defaults to ascending.
You can specify multiple comma-separated columns. Rows are sorted by the first column, then ties by the second, and so on.
SELECT column_name, ... FROM table_name
ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC] ...;
–
Create the following table for an example.
create table user (name text, old integer, address text);
sqlite> create table user (name text, old integer, address text);
sqlite>
Insert data into the table.
insert into user values ('devkuma', 39, 'Seoul');
insert into user values ('kimkc', 34, 'Busan');
insert into user values ('araikuma', 26, 'Seoul');
insert into user values ('happykuma', 19, 'Seoul');
insert into user values ('mykuma', 27, 'Daejeon');
insert into user values ('yourkuma', 28, 'Gwangju');
insert into user values ('raccoon', 31, 'Busan');
sqlite> insert into user values ('devkuma', 39, 'Seoul');
sqlite> insert into user values ('kimkc', 34, 'Busan');
sqlite> insert into user values ('araikuma', 26, 'Seoul');
sqlite> insert into user values ('happykuma', 19, 'Seoul');
sqlite> insert into user values ('mykuma', 27, 'Daejeon');
sqlite> insert into user values ('yourkuma', 28, 'Gwangju');
sqlite> insert into user values ('raccoon', 31, 'Busan');
sqlite>
The following query shows the rows without explicit sorting.
select * from user;
sqlite> select * from user;
name old address
---------- ---------- ----------
devkuma 39 Seoul
kimkc 34 Busan
araikuma 26 Seoul
happykuma 19 Seoul
mykuma 27 Daejeon
yourkuma 28 Gwangju
raccoon 31 Busan
sqlite>
Sort by old in ascending order.
select * from user order by old asc;
The result is ordered by old from smallest to largest.
sqlite> select * from user order by old asc;
name old address
---------- ---------- ----------
happykuma 19 Seoul
araikuma 26 Seoul
mykuma 27 Daejeon
yourkuma 28 Gwangju
raccoon 31 Busan
kimkc 34 Busan
devkuma 39 Seoul
sqlite>
Now sort old in descending order.
select * from user order by old desc;
sqlite> select * from user order by old desc;
name old address
---------- ---------- ----------
devkuma 39 Seoul
kimkc 34 Busan
raccoon 31 Busan
yourkuma 28 Gwangju
mykuma 27 Daejeon
araikuma 26 Seoul
happykuma 19 Seoul
sqlite>
The result is ordered from largest to smallest.
Thus, ORDER BY sorts results ascending or descending by the selected column.
Sorting by Multiple Columns
First, sort only by address.
select * from user order by address asc;
sqlite> select * from user order by address asc;
name old address
---------- ---------- ----------
kimkc 34 Busan
raccoon 31 Busan
mykuma 27 Daejeon
yourkuma 28 Gwangju
devkuma 39 Seoul
araikuma 26 Seoul
happykuma 19 Seoul
sqlite>
Next, sort by address, then by old for rows sharing the same address. The order of columns in ORDER BY determines their priority.
select * from user order by address asc, old asc;
sqlite> select * from user order by address asc, old asc;
name old address
---------- ---------- ----------
raccoon 31 Busan
kimkc 34 Busan
mykuma 27 Daejeon
yourkuma 28 Gwangju
happykuma 19 Seoul
araikuma 26 Seoul
devkuma 39 Seoul
sqlite>
The result is ordered first by address and then by old.