DevBrainiac Web Image
Articles
Blogs
MCQ
SQL
Q&A
Creatives
Tools
Tags
Jobs
Community
LogIn
I don't have account.

ORDER BY clause in SQL

DevSniper

117 Views

ORDER BY clause is a powerful tool for sorting result data set. The ORDER BY clause in SQL allows you to specify sorting mechanism (ascending or descending ). for ascending use ASC and for descending use DESC default is ASC if you did not mention sorting mechanism default will be picked and result set will be in ascending order.

Syntax

SELECT column1, column2, ......
FROM table_name
WHERE condition
ORDER BY columnX [ASC | DESC] , columnY [ASC | DESC] ..........;
  • ORDER BY clause is versatile and can handle sorting of various data types.
  • ORDER BY uses natural ordering/sorting on specified columns based on the data type of the columns.
  • ORDER BY clause allows to write expressions or function results for more complex sorting criteria.
  • ORDER BY clause is used to fetch result data set in ascending or descending order based on specified sorting mechanism.
  • ORDER BY clause utilize indexes on columns to enhance query performance and this feature is very useful for large data set.
  • Try to avoid unnecessary complexity in ORDER BY clauses to maintain query efficiency and readability.
  • Understanding ORDER BY clause is useful for developers to efficiently sort and organize query results based on specified criteria.

Let's assume we have 2 tables Users (Id, Name , Mobile ,Weight, DOB) and UserAddress (Id, UserId, AddressType, Address, Country, City and PostalCode)

Sorting Users by Date of birth

you can fetch users by ascending or descending order of their date of birth. for ascending order use ASC and descending order use DESC. If you don't mention sorting direction the default direction will be picked and default direction of sorting is ASC.

SQL query to fetch users by ascending order of DOB

SELECT Id, Name, Mobile, DOB 
FROM Users
ORDER BY DOB ASC;

            OR
 
SELECT Id, Name, Mobile, DOB 
FROM Users
ORDER BY DOB;

SQL query to fetch users by descending order of DOB

SELECT Id, Name, Mobile, DOB 
FROM Users
ORDER BY DOB DESC;

Sorting by Multiple Columns

You can specify multiple columns in the ORDER BY clause to sort the result set.

SQL query to sort result data set first by Name in ascending order and then by DOB in descending order within each Name.

SELECT Id, Name , DOB
FROM Users 
ORDER BY Name , DOB DESC;

Sorting by using Expressions and Functions

For Complex sorting you can use expression or functions in ORDER BY clause.

SQL query of Complex sorting , first multiplies by 5.2 in user Weight and after that sort on resulting value in descending order.

SELECT Id, Name , DOB
FROM Users 
ORDER BY Weight * 5.2 DESC;