SQLite Order By Clause

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.

SQLite Order By Clause

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.

SQLite Order By Clause Syntax

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 

 

  • expressions - It may be no. of columns or expressions that you want as a result. 
  • tables-list - It may be the list of the table from which you want results. 
  • WHERE conditions - It is optional. It is one or more conditions to retrieve the result. 
  • ASC - It is an optional parameter. It sorts the result set in ascending order by expression. ASC is the default if no modifier is provided. 
  • DESC - It is an optional parameter. It sorts the result set in descending order by expression.

If no modifier is provided with the ORDER BY clause then by default it will sort the result set in ascending order.

SQLite Order By Clause Example

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

SQLite Order By with Column Position

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

SQLite Order By Multiple Columns

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.

Previous
 
Next