SQLite ORDER BY Clause

SQLite is the most popular database engine which is written in C programming language. It is a serverless, easy-to-use relational database system and it is open source and self-contained.

In this article, you will gain knowledge on the SQLite ORDER BY clause. By the end of this article, you will get to know how to use the ORDER BY clause, where to use it, and with what other keyword you are going to use to get the desired output.

ORDER BY Clause

In SQLite, the data that we store in the tables may or may not be stored in the order. So, whenever you try to fetch the data from the table using the select statement then you may not get the desired output. By this, you get a clarity that we use order by clause to get the required output.

ORDER BY clause is used to get the stored data in the sorted order in the result set. Order By clause comes after the From Clause and it helps us to order one or more columns in the ascending or descending order.

• By default the order is ascending, there is no need to use the ASC along with the expression(column) that you want to sort.
• However, you need to specify the DESC to sort the data in descending order.

Syntax:

SELECT expressions
FROM tables
ORDER BY expression [ ASC | DESC ];

Example: ORDER BY Clause

Let’s see some examples of ORDER BY Clause in different types to sort the data.

1. Sorting without using ASC/DESC attribute
2. Sorting by Ascending Order
3. Sorting by Descending Order
4. Sorting by Multiple Columns
5. Sorting by ASC and DESC order at a time
6. sorting by column position
7. Case-Insensitive Sorting
8. Sorting NULL Values

Let us consider the students table and in the below table you can observe that there are four fields they are stu_id, first_name, fees and email and there are four records inserted.

Now, let us see the above examples in detail using this table and apply Order by clause on it.

1. Sorting Without Using ASC/DESC Attribute

Now we are going to sort the data without specifying the ASC/DESC modifier. Then by default the data is ordered in the ascending order (ASC).

Example:

SELECT * FROM students
ORDER BY first_name;

The SQLite ORDER BY clause returns all the records from the students table as we have used the select statement with the (*) and fetches the first_name field in the ascending order.

students without asc/desc table

Here in the above output you can see that the first_name column is arranged in ascending order as you can compare the difference between the original students table and the above output that Stu_id is 4 whose name is Hari is arranged in the second row as H letter is smaller than R

2. Sorting by ASC Order

Now we are going to sort the data with specifying the ASC modifier. Then the data is ordered in the ascending order (ASC).

Example:

select * from students
order by first_name ASC;

The SQLite ORDER BY clause returns all the records from the students table as we have used the select statement with the (*) and fetching the first_name field in the ascending order.

Here in the above output you can see that the first_name column is arranged in ascending order as you can compare the difference between the original students table and both the outputs with using ASC or without using ASC are same.

3. Sorting by DESC Order

Now we are going to sort the data with specifying the DESC modifier. Then the data is ordered in the descending order (DSC).

Example:

select * from students
order by first_name DESC;

The SQLite ORDER BY clause returns all the records from the students table as we have used the select statement with the (*) and fetching the first_name field in the descending order.

Here in the above output you can see that the first_name column is arranged in descending order that means the R letter ASCII value is graeter and there you observe two names starting with R and so we get a doubt that why does not Ram is arranged in first row, then the concepts in this is the next character is checked and thay have same letter A in the both names and next is n in Rani and m in Ram so letter n ASCII value is greater the m. So, Rani is arranged in first row.

4. Sorting by ASC and DESC Order at a Time

Now we are going to use both ascending and descending attribute on our students table at a time and let us see how does it works

Example:

select * from students
order by first_name DESC, fees ASC;

The SQLite ORDER BY clause returns all the records from the students table as we have used the select statement with the (*) and fetching the first_name field in the descending order and fees field in the ascending order.

Here in the above output the first_name is arranged in descending order and fees column is arranged in ascending order and such the four records are fetched.

5. Sorting by Multiple Columns

We use the comma ( , ) to seperate the two columns and this ORDER BY clause sort the columns from left to right. Then the column or the expression that we write first is sorted first and then according to result the second column sorts the row of the table.

Example:

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

select first_name, fees
from students
order by first_name DESC, fees ASC;

In the syntax we are using the select statement to fetch the data, the tablename from which you are going to fetch the data and followed by Order By clause followed by the column name and how you would like to sort either by ascending or descending.

first_name and fees column

In the above output you can see that first_name and fees columns are sorted according to the query that we have applied.

6. Sorting by Column Position

Instead of using the columns name, we can specify the columns position. Here first_name is in the 2nd column and we are going to sort the 2nd column in ascending order by default.

Example:

select * from students
order by 2;

The SQLite ORDER BY clause returns all the records from the students table as we have used the select statement with the (*) and fetching the 2nd column i.e first_name in ascending order by using the column position.

In the above output you can see that the first_name column is arranged in ascending order even without specifying it’s name i.e just by specifying the column position.

7. Case Insensitive Sorting

As we know that sorting can be done by using ASC/DESC and according to that the sorting is done. In case sentisitive sorting the columns are sorted according to the case of the letter i.e upper or lower case. SQLite is a case sensitive by default.

For example, let us add four more columns to our existing column and the table looks like

Updated students table

Example:

SELECT first_name 
FROM students 
ORDER BY first_name COLLATE NOCASE ASC;

Here we are using COLLATE NOCASE is the keyword that we use in the sqlite and first_name is the column on which we are implementing our query and the output looks like as below.

case insensitive first_name

There in the above output you can see that the names are sorted in case sensitive and this is how the case insensitive sorting works.

8. Sorting by NULL Values

NULL values are always smaller than the non-null values and thus the ordering depends on the sorting order that you going to specify. Now we are going to sort the NULL values by using the below syntax.

select email from students
order by email;

Here we are sorting by email column and by default the order is ascending, and hence the output contains NULL values in the first rows and followed by the column values.

email asc

Now let us use the DESC to sort the column in descending order.

select email from students
order by email DESC;

email desc

This how the column with values are arranged first and the NULL values are sorted bottom.

Conclusion

After reading this we get to know that SQLite order by clause is used to arrange the rows in the particular order in result set.Not only that the default modifier is ascending order, whether you do not specify the modifier then the rows are arranged in ascending order in result set.We can also access the column position instead of column names and arrange the data.By reading this article you will definitely get the knowledge on the SQLite Order By clause.