Here we will learn sqlite order by clause with example and sqlite order by column number, sqlite order by multiple columns in ascending or descending order with example.
In SQLite ORDER BY clause is used to sort column records either in ascending or descending order.
Generally, the SQLite tables will store data in unspecified order and it will return records in the same unspecified order while fetching data using SQLite select statement. In SQLite, by using Order By clause we can sort SQLite select statement result set either in ascending or descending order based on our requirement.
Following is the syntax of using the ORDER BY clause with a select statement to sort column records.
SELECT expressions
FROM tables-list
[WHERE conditions]
ORDER BY column1, column2,... [ ASC | DESC ];
In above SQLite Order By clause syntax, we defined few properties those are
If no modifier is provided with the ORDER BY clause then by default it will sort the result set in ascending order.
To use SQLite Order By clause with a select statement to sort column values first create emp_master table and insert some data like as shown below.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel',10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 500000,3);
Now run the following query to check records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
Now, let’s look at the example of the SQLite ORDER BY clause in Select statement to sort employees based on their salary in descending order.
SELECT *
FROM emp_master
ORDER BY SALARY DESC;
If you observe above SQLite Order By clause we added ORDER BY SALARY DESC to sort emp_master table records based on salary in descending order.
When we run the above query we will get result set in which employees whose salary is highest will display on top like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
8 Khyati Shah 50000 3
3 Vinay Jariwala 35100 3
2 Shweta Jariwala 19300 2
6 Sonal Menpara 13000 1
5 Shweta Rana 12000 3
1 Honey Patel 10100 1
7 Yamini Patel 10000 2
4 Jagruti Viras 9500 2
Now let’s look at the example of sorting with relative position. You can also use ORDER BY clause to sort the result set by relative position in which the first field in the result set is 1. The next field is 2, and so on.
SELECT last_name, first_name
FROM emp_master
ORDER BY 2 DESC;
In the above SQLite Select Order By statement we defined ORDER BY 2 DESC so it will sort table records based on the second field (first_name) in descending order. The following is the result of the above query.
last_name first_name
---------- ----------
Patel Yamini
Jariwala Vinay
Menpara Sonal
Jariwala Shweta
Rana Shweta
Shah Khyati
Viras Jagruti
Patel Honey
Now let’s look at the example of SQLite Order by multiple columns in the select statement. Suppose if you want to sort one column in ascending order and another column in descending order then by using SQLite Order By clause in a select statement we can achieve this functionality. We need to write the query like as shown below.
SELECT *
FROM emp_master
ORDER BY SALARY DESC, dept_id ASC;
The above SQLite Order By query will sort records based on SALARY and dept_id columns and the result will be like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
8 Khyati Shah 50000 3
3 Vinay Jariwala 35100 3
2 Shweta Jariwala 19300 2
6 Sonal Menpara 13000 1
5 Shweta Rana 12000 3
1 Honey Patel 10100 1
7 Yamini Patel 10000 2
4 Jagruti Viras 9500 2
This is how we can use SQLite Order By clause to sort table records either in ascending or descending based on our requirements.