ORDER BY Clause
Introduction
We often need the records in the table to be sorted in some order. If the records are arranged in increasing order of some column, then it is called ascending order, and if the records are arranged in decreasing order of some column, then it is called descending order. For getting the sorted records in the table, we use the ORDER BY command. By default, the ORDER BY keyword sorts the records in ascending order.
Ordering according to only one column:
Syntax:
SELECT col1,col2,..coln FROM table_name [WHERE condition] [ORDER BY col1, col2, .. coln] [ASC | DESC]; |
Here
SELECT col1,...coln FROM `table_name` is the command that tells the MySQL server to select the col1,..coln from `table_name.`
(column_1,column_2,…) specifies the columns to select from.
Here ASC is for ascending order display, and DESC is for descending order display.
Example
Consider the following student’s records table. What will be the SQL query command if we wish to display the table by ascending order (increasing order) of roll no.?
Roll_No | Marks | Name | Address | City |
3 | 87 | AAA | M.G. road | Pune |
1 | 87 | DDD | Chandani chowk | Delhi |
2 | 63 | GGG | Viman Nagar | Mumbai |
4 | 55 | HHH | Kuber Nagar | Ahmedabad |
The query for the above question will be as follows:
SELECT * FROM students_details ORDER BY Roll_No ASC; |
The above query will result in the following output.
Roll_No | Marks | Name | Address | City |
1 | 87 | DDD | Chandani chowk | Delhi |
2 | 63 | GGG | Viman Nagar | Mumbai |
3 | 87 | AAA | M.G. road | Pune |
4 | 55 | HHH | Kuber Nagar | Ahmedabad |
In the same example, if we want to sort in descending order, we have to use DESC in place of ASC.
SELECT * FROM students_details ORDER BY Roll_No DESC; |
The above query will result in the following output.
Roll_No | Marks | Name | Address | City |
4 | 55 | HHH | Kuber Nagar | Ahmedabad |
3 | 87 | AAA | M.G. road | Pune |
2 | 63 | GGG | Viman Nagar | Mumbai |
1 | 87 | DDD | Chandani chowk | Delhi |
Ordering according to multiple columns:
We can also order from the table according to multiple columns also by using the (,) operator in the syntax as follows:
Syntax:
SELECT col1,col2,..coln FROM table_name [WHERE condition] ORDER BY col1 ASC|DESC, col2 ASC|DESC; |
Here
SELECT col1,...coln FROM `table_name` is the command that tells the MySQL server to select the col1,..coln from `table_name.`
(column_1,column_2,…) specifies the columns to select from.
Example
Consider the same student’s records table. What will be the command if we wish to display the table by descending order of marks and by ascending order of roll no. as well?
SELECT * FROM Student_details ORDER BY Marks DESC, ROLL_NO ASC; |
The above query will result in:
Roll_No | Marks | Name | Address | City |
1 | 87 | DDD | Chandani chowk | Delhi |
3 | 87 | AAA | M.G. road | Pune |
2 | 63 | GGG | Viman Nagar | Mumbai |
4 | 55 | HHH | Kuber Nagar | Ahmedabad |
FAQs
1. What is the syntax if we want to retrieve data in a specific order from the table?
SELECT col1,col2,..coln FROM table_name [WHERE condition] [ORDER BY col1, col2, .. coln] [ASC | DESC]; |
Here
SELECT col1,...coln FROM `table_name` is the command that tells the MySQL server to select the col1,..coln from `table_name.`
(column_1,column_2,…) specifies the columns to select from.
Here ASC is for ascending order display, and DESC is for descending order display.
2. What is the purpose of the ORDER BY clause?
For getting the sorted records in the table, we use the ORDER BY command. The ORDER BY keyword sorts the records in ascending order by default.
3. Can we use the where clause after ORDER BY?
Yes, we can use the WHERE clause with or without the ORDER BY statement. We can filter records by finite values, comparison values, or with sub-SELECT statements. The WHERE clause gives you several options when filtering data.
4. What is the difference between GROUP BY and ORDER BY?
Group by clause is used to group the rows that have the same value, whereas Order by clause sort the result-set either in ascending or in descending order.
Key Takeaways
In this blog, we learned about the ORDER BY clause in SQL queries.
We begin with learning the syntax of these statements and how to use them in queries with various examples.
Visit here to learn more about different topics related to database management systems. Also, try CodeStudio to practice programming problems for your complete interview preparation. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.
Comments
No comments yet
Be the first to share what you think