Open In Colab

< Previous Page | Home Page | Next Page >

Open in Colab

ORDER BY Clause

The SELECT Statement returns the rows in no particular order. With the help of the ORDER BY clause, we can order the rows in a desired order.

The Syntax looks as follows:

SELECT 
  column_name1, 
  column_name2, .... 
FROM 
  table_name
WHERE 
  Condition 
ORDER BY 
  column_name1, column_name2, .... (ASC OR DESC);

Here ASC is used for sorting in ascending order and DESC for descending order.


ORDER BY with one input

Lets consider our STUDENT table once again:


ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur

The following SQL statement will fetch all the records from the table “STUDENT” and the returned rows will be in ascending order of student age.

SELECT * 
FROM 
  STUDENT
ORDER BY 
  AGE ASC;

Note: The default order of sorting is ascending so the rows would have been sorted in an ascending order, even if we had not used the ASC keyword.

Output:

ROLL_NO NAME AGE BRANCH CITY
10002 Richard 21 ECE Delhi
10001 Kate 22 CSE Mumbai
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur
10004 Peter 26 CSE Delhi
10003 Rick 33 ME Chennai


The following SQL statement will fetch the student names from the table “STUDENT” and the returned names will be sorted in descending order.

SELECT NAME 
   FROM STUDENT
ORDER BY 
  NAME DESC;

Result:

NAME
Rick
Richard
Peter
Peter
Mark
Kate
Brad


ORDER BY with Multiple Columns

In the above examples, we have done the sorting based on a single column. Obviously, we can also use multiple columns in an ORDER BY clause.

In case you want to sort the result set by multiple columns, you use a comma (,) to separate two 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.

Let's sort the STUDENT table based on the city first and then by age within each city:

SELECT * 
   FROM STUDENT
ORDER BY 
  CITY ASC, 
  AGE ASC;
ROLL_NO NAME AGE BRANCH CITY
10003 Rick 33 ME Chennai
10002 Richard 21 ECE Delhi
10004 Peter 26 CSE Delhi
10006 Mark 22 ECE Jaipur
10001 Kate 22 CSE Mumbai
10005 Peter 22 CSE Noida
10007 Brad 23 ME Rampur


Sorting Nulls

Sorting NULLs A NULL value denotes that the information is missing or the data is not applicable.

NULL is special because you cannot compare it with another value. Simply put, if two pieces of information are unknown, you cannot compare them.

When it comes to sorting, SQLite considers NULL to be smaller than any other value.

It means that NULLs will appear at the beginning of the result set if you use ASC or at the end of the result set when you use DESC.

SQLite 3.30.0 added the NULLS FIRST and NULLS LAST options to the ORDER BY clause. The NULLS FIRST option specifies that the NULLs will appear at the beginning of the result set while the NULLS LAST option place NULLs at the end of the result set.

< Previous Page | Home Page | Next Page >

Open in Colab