SQL ORDER BY
SQL ORDER BY
The SQL ORDER BY clause is used to sort the data stored in tables in the database. The sorting can be done in an ascending way, descending way, or based on multiple columns of the table.
Generally, the default sorting order is ascending order.
SELECT expressions FROM tables WHERE conditions ORDER BY expression ASC | DESC;
The following is the syntax of a general ORDER BY clause:
Example:
Let’s consider the following Customers table.
ID | NAME | AGE | ADDRESS | SALARY |
1 | Himani gupta | 21 | Modinagar | 22000 |
2 | Shiva tiwari | 22 | Bhopal | 21000 |
3 | Ajeet bhargav | 45 | Meerut | 65000 |
4 | Ritesh yadav | 36 | Azamgarh | 26000 |
5 | Balwant singh | 45 | Varanasi | 36000 |
6 | Mahesh sharma | 26 | Mathura | 22000 |
SELECT * FROM Customers ORDER BY NAME, SALARY;
The above query will sort all the records in ascending order based on the NAME and SALARY columns.
Output:
ID | NAME | AGE | ADDRESS | SALARY |
3 | Ajeet bhargav | 45 | Meerut | 65000 |
5 | Balwant singh | 45 | Varanasi | 36000 |
1 | Himani gupta | 21 | Modinagar | 22000 |
6 | Mahesh sharma | 26 | Mathura | 22000 |
4 | Ritesh yadav | 36 | Azamgarh | 26000 |
2 | Shiva tiwari | 22 | Bhopal | 21000 |
SELECT * FROM CUSTOMERS ORDER BY NAME DESC;
The above query will sort all the records in the table in a descending manner based on the NAME column.
Output:
ID | NAME | AGE | ADDRESS | SALARY |
2 | Shiva tiwari | 22 | Bhopal | 21000 |
4 | Ritesh yadav | 36 | Azamgarh | 26000 |
6 | Mahesh sharma | 26 | Mathura | 22000 |
1 | Himani gupta | 21 | Modinagar | 22000 |
5 | Balwant singh | 45 | Varanasi | 36000 |
3 | Ajeet bhargav | 45 | Meerut | 65000 |
There are various topics under SQL ORDER BY clause. Namely, they are:
- SQL ORDER BY ASC – Sorts the data in ascending manner
- SQL ORDER BY DESC – Sorts the data in descending manner
- SQL ORDER BY RANDOM – Sorts random data
- SQL ORDER BY LIMIT – Selects limited data from the database
- SQL ORDER BY Multiple Columns – Sorts the data based on multiple columns.
Let’s discuss each of them in detail.
SQL ORDER BY ASC
SQL ORDER BY ASC clause sorts the data in ascending order based on some columns. Generally, the default sorting order is ascending manner.
Example:
Let’s consider the following Customers table.
ID | NAME | AGE | ADDRESS | SALARY |
1 | Himani gupta | 21 | Modinagar | 22000 |
2 | Shiva tiwari | 22 | Bhopal | 21000 |
3 | Ajeet bhargav | 45 | Meerut | 65000 |
4 | Ritesh yadav | 36 | Azamgarh | 26000 |
5 | Balwant singh | 45 | Varanasi | 36000 |
6 | Mahesh sharma | 26 | Mathura | 22000 |
SELECT * FROM Customers ORDER BY NAME, SALARY;
The above query will sort all the records in ascending order based on the NAME and SALARY columns.
Output:
ID | NAME | AGE | ADDRESS | SALARY |
3 | Ajeet bhargav | 45 | Meerut | 65000 |
5 | Balwant singh | 45 | Varanasi | 36000 |
1 | Himani gupta | 21 | Modinagar | 22000 |
6 | Mahesh sharma | 26 | Mathura | 22000 |
4 | Ritesh yadav | 36 | Azamgarh | 26000 |
2 | Shiva tiwari | 22 | Bhopal | 21000 |
SQL ORDER BY DESC
SQL ORDER BY DESC clause sorts the data in descending order based on some columns.
Example:
Let’s consider the following Customers table.
ID | NAME | AGE | ADDRESS | SALARY |
1 | Himani gupta | 21 | Modinagar | 22000 |
2 | Shiva tiwari | 22 | Bhopal | 21000 |
3 | Ajeet bhargav | 45 | Meerut | 65000 |
4 | Ritesh yadav | 36 | Azamgarh | 26000 |
5 | Balwant singh | 45 | Varanasi | 36000 |
6 | Mahesh sharma | 26 | Mathura | 22000 |
SELECT * FROM CUSTOMERS ORDER BY NAME DESC;
The above query will sort all the records in the table in a descending manner based on the NAME column.
Output:
ID | NAME | AGE | ADDRESS | SALARY |
2 | Shiva tiwari | 22 | Bhopal | 21000 |
4 | Ritesh yadav | 36 | Azamgarh | 26000 |
6 | Mahesh sharma | 26 | Mathura | 22000 |
1 | Himani gupta | 21 | Modinagar | 22000 |
5 | Balwant singh | 45 | Varanasi | 36000 |
3 | Ajeet bhargav | 45 | Meerut | 65000 |
SQL ORDER BY LIMIT
The SQL ORDER BY LIMIT clause helps in retrieving specific number of records from the database table. It is generally used in situations where there are large number of tuples which satisfy the given condition. It sets an upper limit to the number of records that are being retrieved.
In order to use the LIMIT clause in SQL, one has to use the ROWNUM clause with it.
Note: It should be noted that the LIMIT clause is not supported by all SQL versions.
This clause can be specified using the SQL 2008 OFFSET or FETCH FIRST clauses.
Example:
Let’s consider the following Student table.
RollNo | Name | Grade |
12001 | Aditya | 9 |
12002 | Sahil | 6 |
12003 | Hema | 8 |
12004 | Robin | 9 |
12005 | Sita | 7 |
12006 | Anne | 10 |
12007 | Yusuf | 7 |
12008 | Alex | 5 |
SELECT * FROM Student LIMIT 5;
The above query will only return the top 5 students from the Student table.
Output:
12001 | Aditya | 9 |
12002 | Sahil | 6 |
12003 | Hema | 8 |
12004 | Robin | 9 |
12005 | Sita | 7 |
The LIMIT clause can also be used with the ORDER BY clause.
SELECT * FROM Student ORDER BY Grade DESC LIMIT 3;
The above query will return the top 3 students having the highest grades in descending order.
Output:
12006 | Anne | 10 |
12001 | Aditya | 9 |
12004 | Robin | 9 |
The LIMIT operator is especially useful in situations where limited data needs to be retrieved without using any conditional statements.
The LIMIT operator can also be used with the OFFSET operator.
LIMIT with OFFSET
It is to be noted that the OFFSET value should be greater than zero, and it cannot be negative. If a negative value is supplied, it will return an error.
Example:
Let’s consider the same Student table.
RollNo | Name | Grade |
12001 | Aditya | 9 |
12002 | Sahil | 6 |
12003 | Hema | 8 |
12004 | Robin | 9 |
12005 | Sita | 7 |
12006 | Anne | 10 |
12007 | Yusuf | 7 |
12008 | Alex | 5 |
SELECT * FROM Student LIMIT 5 OFFSET 2 ORDER BY RollNo;
This query will return the top 5 records of the Student table excluding the top 2 entries.
Output:
RollNo | Name | Grade |
12003 | Hema | 8 |
12004 | Robin | 9 |
12005 | Sita | 7 |
12006 | Anne | 10 |
12007 | Yusuf | 7 |
LIMIT ALL
There is another clause similar to LIMIT known as LIMIT ALL. The LIMIT ALL clause will not put any limit and will return all the entries of the table.
Example:
Let’s again consider the same Student table.
RollNo | Name | Grade |
12001 | Aditya | 9 |
12002 | Sahil | 6 |
12003 | Hema | 8 |
12004 | Robin | 9 |
12005 | Sita | 7 |
12006 | Anne | 10 |
12007 | Yusuf | 7 |
12008 | Alex | 5 |
Query:
SELECT * FROM Student LIMIT ALL;
RollNo | Name | Grade |
12001 | Aditya | 9 |
12002 | Sahil | 6 |
12003 | Hema | 8 |
12004 | Robin | 9 |
12005 | Sita | 7 |
12006 | Anne | 10 |
12007 | Yusuf | 7 |
12008 | Alex | 5 |
SQL ORDER BY Multiple Columns
The SQL ORDER BY Multiple Columns will return the data entries in the column by sorting them using multiple columns which have been given in the query.
It will sort the data based on the order of the column names which have been supplied after the ORDER BY clause. The name of the columns should be added in the corresponding order in which the user wants the sorting to happen.
Multiple column names can be added by separating them using a comma (,). Also, the ASC or DESC keywords can be used to specify the order of the sorting.
Example:
Let’s consider the following Employee table having four columns namely id, first_name, last_name, and salary.
id | first_name | last_name | salary |
1 | Lisa | Ulman | 3000 |
2 | Ada | Muller | 2400 |
3 | Thomas | Green | 2400 |
4 | Michael | Muller | 3000 |
5 | Mary | Green | 2400 |
Query:
SELECT id, first_name, last_name, salary FROM employee ORDER BY salary DESC, last_name;
Output:
id | first_name | last_name | salary |
4 | Michael | Muller | 3000 |
1 | Lisa | Ulman | 3000 |
3 | Thomas | Green | 2400 |
5 | Mary | Green | 2400 |
2 | Ada | Muller | 2400 |
Hence, first, the output has been sorted by salary in descending order and then by last name in ascending order. It is to be noted that if ASC or DESC is not mentioned, it will, by default, sort the both the columns in ascending order.
Conclusion
So, above are some of the most important and frequently used ORDER BY clauses that are used by developers daily in order to perform specific operations on the database and retrieve out data. These clauses can be used individually or they can also be used together, in a combined way, to carry out database operations.