SQL Multiple Order BY clause By Practical Examples
Summary: in this tutorial, you will learn how to use the SQL Multiple Order BY clause to sort the result set of a query by one, two or more columns.
Introduction to the SQL Multiple Order BY clause
When you use the SELECT statement to query data from a table, returns the rows in no particular order. Therefore, means that the order of rows in the result set is not guaranteed. we can say that SQL can return a result set with an unspecified order of rows. but when you use the SQL ORDER BY clause, then we can order the rows in the desired order.
SQL Server ORDER BY clause is used to sort the returned records in the desired order. Furthermore, By using the SQL Server ORDER BY statement, we can also sort the result in ascending or descending sequence. This clause also can be used with one, or multiple columns as well.
SQL Multiple Order BY Syntax
FROM table_name
WHERE Condition
ORDER BY column_name1, column_name2, …. (ASC OR DESC);
column_name | expression
First, you would like to specify a column name on which you would like to sort the result set of the query. Furthermore, if you SELECT one or more multiple columns, then the result set is sorted by the 1st column and then that sorted result set is sorted by the 2nd column, and so on.
The columns that seem within the ORDER BY statement should correspond to either column in the select list or to columns defined in the table specified in the FROM clause.
ASC | DESC
In SQL Server you can use ASC or DESC to specify whether or not the values within the specified column should be sorted in ascending or descending order.
The ASC in SQL Server is used to sort the result data from the lowest value to the highest value while the other DESC in SQL Server is used to sort the result data set from the highest value to the lowest one.
If you don’t explicitly specify ASC or DESC, then SQL uses ASC as the default type sort order. SQL Server treats NULL because of the lowest price.
When processing is the SELECT statement that has an ORDER BY clause, the ORDER BY SQL statement is that the last Statement to be processed.
DEMO TABLE: STUDENT
Let’s Consider an “STUDENT” table with the following data.
| ROLL_NO | NAME | FATHER | AGE | BRANCH | CITY |
|---|---|---|---|---|---|
| 202111 | Alfreds | Futterkiste | 21 | CSE | Mumbai |
| 202112 | BlauerSee | Delikatessen | 22 | ECE | Delhi |
| 202113 | Ana | Trujillo | 23 | ME | Chennai |
| 202114 | Victoria | Ashworth | 26 | CSE | Delhi |
| 202115 | Elizabeth | Lincoln | 21 | CSE | Noida |
| 202116 | NIliza | BrownLee | 22 | ECE | Jaipur |
| 202117 | Pedro | Afonso | 23 | ME | Lucknow |
| 202118 | Aditya | Dinesh | 22 | EN | Jhasi |
| 202119 | Pinki | Gupta | 23 | EN | Kanpur |
| 202120 | Ritu | Thappa | 22 | ME | Lucknow |
A) SQL ORDER BY Clause Example
The given below following SQL statement will fetch all the records from the table “STUDENT” and it will be returned rows in ascending order of student AGE.
|
1 2 3 |
SELECT * FROM STUDENT ORDER BY AGE; |
B) SQL ORDER BY DESC Example
The following given below SQL statement will fetch the student “NAME” from the table “STUDENT” and it will be returned names, sorted in descending order (we have used DESC for descending order in the ORDER BY clause).
|
1 2 3 |
SELECT NAME FROM STUDENT ORDER BY NAME DESC; |
In the given above example, as you have seen, that we have got a list of student names sorted in the descending order
C) SQL ORDER BY Multiple Columns
Furthermore, in the above examples, as you have seen that we have done the sorting-based example on a single column. However, we can use multiple columns in the SQL Server ORDER BY clause.
Therefore, when multiple columns are used in the ORDER BY clause, first the rows will be sorted based on the 1st column and then by the 2nd column. Let us understand the SQL ORDER BY Multiple Columns example.
DEMO TABLE: EMPLOYEE
Furthermore, Let’s Consider an “EMPLOYEE” table with the following data.
| EMP_ID | EMP_NAME | CITY | COUNTRY | EMP_SALARY | PINCODE |
|---|---|---|---|---|---|
| 202111 | Ravindra | Noida | India | 35000 | 226023 |
| 202116 | Ashish | Jhasi | India | 38000 | 221621 |
| 202114 | Vivek | Lucknow | India | 42000 | 226016 |
| 202112 | Saumya | Ghaziabad | India | 47000 | 221310 |
| 202115 | Jitendra | Kanpur | India | 36000 | 228880 |
| 202113 | Priyanshu | Sultanpur | India | 39000 | 602211 |
I) Let us sort the rows based on the city 1st and then by pin code within each city:
|
1 2 3 |
SELECT * FROM EMPLOYEE ORDER BY CITY, PINCODE; |
As you can seen in the output screenshot result that the rows are sorted by CITY and then within CITY sorted by PINCODE.
II) Again, let us sort the rows based on the city 1st in ascending order after that by pin code in descending order within each city:
|
1 2 3 |
SELECT * FROM EMPLOYEE ORDER BY CITY ASC, PINCODE DESC; |
Therefore, as you can see in the screenshot result that the rows are sorted by CITY in ascending order and then within city sorted in reverse order by pincode.
Conclusion:
In this article, you have learned how to use the SQL Multiple Order BY clause to sort the result set of a query by one, two or more columns with the different types of examples. I hope you will enjoy it!
About Author
