ORDER BY Clause in SQL

The ORDER BY clause in SQL is used to sort the result set of a query in ascending or descending order based on one or more columns. It is often used to organize data in a meaningful way, such as sorting names alphabetically or numbers numerically.

Key Points

1. Sorting Data: The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order.
2. Multiple Columns: You can sort by multiple columns, with the primary sort column listed first.
3. Default Order: If no order is specified, the default is ascending (ASC).
4. The ORDER BY clause is used with the SELECT statement.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• column1, column2, ...: The columns you want to retrieve.
• table_name: The table from which to retrieve data.
• ASC: Sorts the result set in ascending order (default).
• DESC: Sorts the result set in descending order.

Examples

Suppose you have a table named Employees that stores employee details.

Table: Employees

EmployeeID	Name	City	State	Salary
1	Anand	Chennai	Tamil Nadu	50000
2	Bala	Coimbatore	Tamil Nadu	60000
3	Kavitha	Karaikal	Puducherry	55000
4	Raj	Madurai	Tamil Nadu	70000
5	Kumar	Trichy	Tamil Nadu	45000

To retrieve employees sorted by their names in ascending order:

SELECT Name, City, Salary
FROM Employees
ORDER BY Name ASC;

Name	City	Salary
Anand	Chennai	50000
Bala	Coimbatore	60000
Kavitha	Karaikal	55000
Kumar	Trichy	45000
Raj	Madurai	70000

Sorting in Descending Order

To sort the employees by their salary in descending order:

SELECT Name, City, Salary
FROM Employees
ORDER BY Salary DESC;

Name	City	Salary
Raj	Madurai	70000
Bala	Coimbatore	60000
Kavitha	Karaikal	55000
Anand	Chennai	50000
Kumar	Trichy	45000

Sorting by Multiple Columns

You can sort by multiple columns. For example, sort employees first by their state in ascending order and then by their salary in descending order.

SELECT Name, City, State, Salary
FROM Employees
ORDER BY State ASC, Salary DESC;

Name	City	State	Salary
Kavitha	Karaikal	Puducherry	55000
Raj	Madurai	Tamil Nadu	70000
Bala	Coimbatore	Tamil Nadu	60000
Anand	Chennai	Tamil Nadu	50000
Kumar	Trichy	Tamil Nadu	45000

Using ORDER BY with WHERE Clause

You can combine the ORDER BY clause with the WHERE clause to filter and sort data. For example, retrieve employees from Tamil Nadu and sort them by salary in descending order.

SELECT Name, City, Salary
FROM Employees
WHERE State = 'Tamil Nadu'
ORDER BY Salary DESC;

Name	City	Salary
Raj	Madurai	70000
Bala	Coimbatore	60000
Anand	Chennai	50000
Kumar	Trichy	45000

Practical Use Case

Suppose you have a table named Students that stores student details, and you want to retrieve students sorted by their percentage in descending order.

Table: Students

StudentID	Name	City	State	Percentage
1	Ram	Chennai	Tamil Nadu	92
2	Karthik	Coimbatore	Tamil Nadu	88
3	David	Bangalore	Karnataka	95
4	Kannan	Karaikal	Puducherry	91
5	Siva	Madurai	Tamil Nadu	89

SELECT Name, City, Percentage
FROM Students
ORDER BY Percentage DESC;

Name	City	Percentage
David	Bangalore	95
Ram	Chennai	92
Kannan	Karaikal	91
Siva	Madurai	89
Karthik	Coimbatore	88

Key Takeaways

1. The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order.
2. You can sort by one or more columns, with the primary sort column listed first.
3. The default sorting order is ascending (ASC).
4. The ORDER BY clause can be combined with the WHERE clause to filter and sort data.
5. Sorting data makes it easier to analyze and interpret query results.