ORDER BY Clause
The ORDER BY clause is used to sort the result of a SQL query in ascending or descending order.
By default, ORDER BY sorts data in ascending order. We can also use the DESC keyword to sort the data in descending order.
What is ORDER BY Clause?
The ORDER BY clause is used to arrange records returned by a SELECT query according to one or more columns.
It is useful when we want to display data in a specific sequence such as lowest to highest, highest to lowest, A to Z or Z to A.
Syntax
SELECT column1, column2
FROM table_name
ORDER BY column_name;
Example Table
| StudentId | StudentName | Age | City | Fees |
|---|---|---|---|---|
| 101 | Rahul | 22 | Pune | 35000 |
| 102 | Priya | 23 | Mumbai | 40000 |
| 103 | Amit | 21 | Nashik | 30000 |
| 104 | Neha | 24 | Pune | 45000 |
ORDER BY ASC
The ASC keyword sorts the records in ascending order.
SELECT *
FROM Students
ORDER BY Age ASC;
The youngest student will appear first and the oldest student will appear last.
ORDER BY DESC
The DESC keyword sorts the records in descending order.
SELECT *
FROM Students
ORDER BY Age DESC;
The oldest student will appear first and the youngest student will appear last.
Default Sorting
If we do not specify ASC or DESC, SQL Server uses ascending order by default.
SELECT *
FROM Students
ORDER BY Age;
The above query is equivalent to using ORDER BY Age ASC.
ORDER BY with Character Data
ORDER BY can also be used with character or string columns.
SELECT *
FROM Students
ORDER BY StudentName ASC;
This sorts the student names alphabetically.
ORDER BY with Fees
SELECT *
FROM Students
ORDER BY Fees DESC;
This displays the student having the highest fees first.
ORDER BY Multiple Columns
We can sort records using more than one column.
SELECT *
FROM Students
ORDER BY City ASC, Age DESC;
First, records are sorted by City in ascending order. Within the same city, records are sorted by Age in descending order.
ORDER BY with WHERE
ORDER BY can be combined with WHERE to filter records and then sort the filtered result.
SELECT *
FROM Students
WHERE City = 'Pune'
ORDER BY Age DESC;
This query first selects students from Pune and then sorts them by age from highest to lowest.
ORDER BY using Column Position
SQL Server also allows sorting using the position of a column in the SELECT list.
SELECT StudentName, Age, City
FROM Students
ORDER BY 2 DESC;
Here, 2 represents the second column selected, which is Age.
Important
- ORDER BY is used to sort query results.
- ASC sorts data in ascending order.
- DESC sorts data in descending order.
- ASC is the default sorting order.
- Multiple columns can be used with ORDER BY.
- ORDER BY can be combined with WHERE.
Summary
In this section, we learned how the ORDER BY clause is used to sort SQL Server query results. We covered ascending and descending order, sorting by multiple columns, combining ORDER BY with WHERE, and sorting using column position.