SQL
[Link]
ORDER BY
• Whenever we want to sort the records based on the columns stored in the
tables of the SQL database, then we consider using the ORDER BY clause in
SQL.
• The ORDER BY clause in SQL will help us to sort the records based on the
specific column of a table. This means that all the values stored in the column
on which we are applying ORDER BY clause will be sorted, and the
corresponding column values will be displayed in the sequence in which we
have obtained the values in the earlier step.
• Using the ORDER BY clause, we can sort the records in ascending or
descending order as per our requirement. The records will be sorted in
ascending order whenever the ASC keyword is used with ORDER by
clause. DESC keyword will sort the records in descending order.
• If no keyword is specified after the column based on which we have to sort
the records, in that case, the sorting will be done by default in the ascending
order.
Syntax to sort the records in ascending order:
SELECT ColumnName1,...,ColumnNameN FROM TableName ORD
ER BY ColumnName ASC;
Syntax to sort the records in descending order:
SELECT ColumnName1,...,ColumnNameN FROM TableName OR
DER BY ColumnNameDESC;
©Topperworld
SQL
Syntax to sort the records in ascending order without using ASC
keyword:
SELECT ColumnName1,...,ColumnNameN FROM TableName O
RDER BY ColumnName;
Let us explore more on this topic with the help of examples. We will use the
MySQL database for writing the queries in examples.
Consider we have customers table with the following records:
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
7 Rohit Shrivastav 19 Ahemdabad 38000
8 Neeru Sharma 29 Pune 40000
©Topperworld
SQL
9 Aakash Yadav 32 Mumbai 43500
10 Sahil Sheikh 35 Aurangabad 68800
Example :
Write a query to sort the records in the ascending order of the customer names
stored in the customers table.
Query:
mysql> SELECT *FROM customers ORDER BY Name ASC;
Here in a SELECT query, an ORDER BY clause is applied on the column 'Name' to
sort the records. ASC keyword will sort the records in ascending order.
Output:
ID NAME AGE ADDRESS SALARY
9 Aakash Yadav 32 Mumbai 43500
3 Ajeet Bhargav 45 Meerut 65000
5 Balwant Singh 45 Varanasi 36000
1 Himani Gupta 21 Modinagar 22000
6 Mahesh Sharma 26 Mathura 22000
©Topperworld
SQL
8 Neeru Sharma 29 Pune 40000
4 Ritesh Yadav 36 Azamgarh 26000
7 Rohit Shrivastav 19 Ahemdabad 38000
10 Sahil Sheikh 35 Aurangabad 68800
2 Shiva Tiwari 22 Bhopal 21000
All the records present in the customers table are displayed in the ascending
order of the customer's name.
❖ SQL ORDER BY CLAUSE WITH ASCENDING ORDER
• Whenever we want to sort the records based on the columns stored in
the tables of the SQL database, then we consider using the ORDER BY
clause in SQL.
• The ORDER BY clause in SQL helps us sort the records based on a table's
specific column. This means that initially, all the values stored in the
column on which we are applying the ORDER BY clause will be sorted.
Then the corresponding column values will be displayed in the same
sequence in which the values we have obtained in the earlier step.
• Using the ORDER BY clause, we can sort the records in ascending or
descending order as per our requirement. The records will be sorted in
ascending order whenever the ASC keyword is used with the ORDER by
©Topperworld
SQL
clause. Whereas, DESC keyword will sort the records in descending order.
If no keyword is specified after the column based on which we have to
sort the records, then in that case, the sorting will be done by default in
the ascending order.
Before writing the queries for sorting the records, let us understand the syntax.
Syntax to sort the records in ascending order:
SELECT ColumnName1,…,ColumnNameN FROM TableName
ORDER BY ColumnName ASC;
Syntax to sort the records in ascending order without using ASC
keyword:
SELECT ColumnName1,…,ColumnNameN FROM TableName
ORDER BY ColumnName;
Let us explore more on this topic with the help of examples. We will use the
MySQL database for writing the queries in examples.
Consider we have customers table with the following records:
ID NAME AGE ADDRESS SALARY
1 Himani Gupta 21 Modinagar 22000
2 Shiva Tiwari 22 Bhopal 21000
©Topperworld
SQL
3 Ajeet Bhargav 45 Meerut 65000
4 Ritesh Yadav 36 Azamgarh 26000
5 Balwant Singh 45 Varanasi 36000
6 Mahesh Sharma 26 Mathura 22000
7 Rohit Shrivastav 19 Ahemdabad 38000
8 Neeru Sharma 29 Pune 40000
9 Aakash Yadav 32 Mumbai 43500
10 Sahil Sheikh 35 Aurangabad 68800
Example :
Write a query to sort the records in the ascending order of the customer names
stored in the customers table.
Query:
mysql> SELECT *FROM customers ORDER BY Name
ASC;
Here in a SELECT query, an ORDER BY clause is applied on the column 'Name' to
sort the records. ASC keyword will sort the records in ascending order.
©Topperworld
SQL
Output:
ID Name Age Address Salary
9 Aakash Yadav 32 Mumbai 43500
3 Ajeet Bhargav 45 Meerut 65000
5 Balwant Singh 45 Varanasi 36000
1 Himani Gupta 21 Modinagar 22000
6 Mahesh Sharma 26 Mathura 22000
8 Neeru Sharma 29 Pune 40000
4 Ritesh Yadav 36 Azamgarh 26000
7 Rohit Shrivastav 19 Ahemdabad 38000
10 Sahil Sheikh 35 Aurangabad 68800
2 Shiva Tiwari 22 Bhopal 21000
All the records present in the customers table are displayed in the ascending
order of the customer's name.
©Topperworld
SQL
❖ SQL ORDER BY CLAUSE WITH DESCENDING ORDER
• Whenever we want to sort the records based on the columns stored in
the tables of the SQL database, then we consider using the ORDER BY
clause in SQL.
• The ORDER BY clause in SQL helps us to sort the records based on the
specific column of a table. This means that initially, all the values stored
in the column on which we are applying the ORDER BY clause will be
sorted. Then the corresponding column values will be displayed in the
same sequence in which the values we have obtained in the earlier step.
• Using the ORDER BY clause, we can sort the records in ascending or
descending order as per our requirement. The records will be sorted in
ascending order whenever the ASC keyword is used with the ORDER by
clause. DESC keyword will sort the records in descending order. If no
keyword is specified after the column based on which we have to sort the
records, then, in that case, the sorting will be done by default in the
ascending order.
Before writing the queries for sorting the records, let us understand the syntax.
Syntax to sort the records in descending order:
SELECT ColumnName1,…,ColumnNameN FROM TableNa
me ORDER BY ColumnNameDESC;
Let us explore more on this topic with the help of examples. We will use the
MySQL database for writing the queries in examples.
©Topperworld
SQL
Consider we have customers table with the following records:
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
7 Rohit Shrivastav 19 Ahemdabad 38000
8 Neeru Sharma 29 Pune 40000
9 Aakash Yadav 32 Mumbai 43500
10 Sahil Sheikh 35 Aurangabad 68800
Example :
Write a query to sort the records in the descending order of the customer names
stored in the customers table.
©Topperworld
SQL
Query:
mysql> SELECT *FROM customers ORDER BY Name DESC
;
Here in a SELECT query, an ORDER BY clause is applied on the column 'Name' to
sort the records. DESC keyword will sort the records in descending order.
Output:
ID NAME AGE ADDRESS SALARY
2 Shiva Tiwari 22 Bhopal 21000
10 Sahil Sheikh 35 Aurangabad 68800
7 Rohit Shrivastav 19 Ahemdabad 38000
4 Ritesh Yadav 36 Azamgarh 26000
8 Neeru Sharma 29 Pune 40000
6 Mahesh Sharma 26 Mathura 22000
1 Himani Gupta 21 Modinagar 22000
5 Balwant Singh 45 Varanasi 36000
3 Ajeet Bhargav 45 Meerut 65000
©Topperworld
SQL
9 Aakash Yadav 32 Mumbai 43500
All the records present in the customers table are displayed in the descending
order of the customer's name.
❖ SQL ORDER BY LIMIT
We can retrieve limited rows from the database. I can be used in pagination
where are forced to show only limited records like 10, 50, 100 etc.
LIMIT CLAUSE FOR ORACLE SQL:
If you want to use LIMIT clause with SQL, you have to use ROWNUM queries
because it is used after result are selected.
You should use the following code:
SELECT name, age
FROM
(SELECT name, age, ROWNUM r
FROM
(SELECT name, age, FROM employee_data
ORDER BY age DESC
WHERE ROWNUM <=40
WHERE r >= 21;
©Topperworld
SQL
This query will give you 21th to 40th rows.
❖ SQL SORTING ON MULTIPLE COLUMNS
Let's take an example of customer table which has many columns, the following
SQL statement selects all customers from the table named "customer", stored
by the "country" and "Customer-Name" columns:
SELECT * FROM customers
ORDER BY country, Customer-Name;
©Topperworld
