Order By Clause in SQL Server

Back to: SQL Server Tutorial

Order By Clause in SQL Server with Examples

In this article, I am going to discuss the Order By Clause in SQL Server with examples. Please read our previous article where we discussed the Where Clause in SQL Server with examples. As part of this article, we are going to cover the following concepts related to the SQL Server Order By Clause.

  1. What is Order By Clause and its need in SQL Server?
  2. Parameters or Arguments can use with Order By Clause.
  3. Sorting the Data in Ascending and Descending Order using Order By Clause in SQL Server.
  4. How to Sort the Data by relative position in SQL Server?
  5. How to use both ASC and DESC attributes in a single query?
  6. Understanding the OFFSET and FETCH options used in the order by Clause in SQL Server.
What is Order By Clause and its need in SQL Server?

The Order By Clause in SQL Server is used for sorting the data either in ascending or descending order of a query based on a specified column or list of columns. That means if you want to sort the output or result of a query either in ascending or descending order then you need to use SQL Server Order by Clause. Following is the syntax to use Order By Clause.
SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ASC | DESC];

Parameters or Arguments used with Order By Clause in SQL Server:
  1. Expressions: The columns or calculations that we want to retrieve.
  2. Tables: The tables from which we want to retrieve the records. There should be at least one table specified in the FROM clause.
  3. WHERE Conditions: It is optional. The conditions must be met for the records to be selected by the query.
  4. ASC: It is optional. If you want to sort the result set in ascending order of the expression then you need to use ASC.
  5. DESC: It is optional. If you want to sort the result set in descending order by expression then you need to the DESC keyword.
Points to remember while working with Order By Clause in SQL Server:

By default, the Order By Clause in SQL Server will sort the data in ascending order. If you want to arrange the data in descending order then you must have to use the DESC keyword. The Order By Clause can be applied to any data type column in the table. This clause will arrange the data temporarily but not in the permanent store. The Order By Clause can only be used in Select Statements.

Example to Understand SQL Server Order By Clause:

We are going to use the following Employee table to understand the Order By Clause in SQL Server with Examples.

Order By Clause in SQL Server with Examples

Please use the below SQL script to create and populate the Employee table with sample data.

-- Create Person table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Insert some test data into Person table
INSERT INTO Employee VALUES('PRANAYA','PRANAYA@G.COM','Male', 'IT', 25000, 30,'MUMBAI')
INSERT INTO Employee VALUES('TARUN','TARUN@G.COM','Male', 'Payroll', 30000, 27,'ODISHA')
INSERT INTO Employee VALUES('PRIYANKA','PRIYANKA@G.COM','Female', 'IT', 27000, 25,'BANGALORE')
INSERT INTO Employee VALUES('PREETY','PREETY@G.COM','Female', 'HR', 35000, 26,'BANGALORE')
INSERT INTO Employee VALUES('RAMESH','RAMESH@G.COM','Male','IT', 26000, 27,'MUMBAI')
INSERT INTO Employee VALUES('PRAMOD','PRAMOD@G.COM','Male','HR', 29000, 28,'ODISHA')
INSERT INTO Employee VALUES('ANURAG','ANURAG@G.COM','Male', 'Payroll', 27000, 26,'ODISHA')
INSERT INTO Employee VALUES('HINA','HINA@G.COM','Female','HR', 26000, 30,'MUMBAI')
INSERT INTO Employee VALUES('SAMBIT','HINA@G.COM','Male','Payroll', 30000, 25,'ODISHA')
GO
Sorting the records without using ASC/DESC attribute in SQL Server:

The SQL Server ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the clause, the sort order is defaulted to ASC or ascending order.
SELECT * FROM Employee ORDER BY Name;
The above SQL Server ORDER BY example would return all records sorted by the Name field in ascending order and would be equivalent to the following ORDER BY clause example.
SELECT * FROM Employee ORDER BY Name ASC;
When you execute the above query, you will get the following output. Notice the records are sorted based on the Name in ascending order.

Sorting in Ascending Order using Order By ASC Keyword in SQL Server

Sorting in Descending Order using Order By DESC Keyword in SQL Server:

When we want to sort the result set in descending order then we need to use the DESC attribute in the ORDER BY clause. The below SQL Server ORDER BY example will return all records sorted by the Name field in the descending order whose Gender is Male.
SELECT * FROM Employee WHERE Gender = ‘Male’ ORDER BY Name DESC;
When you execute the above query, you will get the following output. Notice, the above returns only the Male employees and then sort the employees by name in descending order.

Sorting in Descending Order using Order By DESC Keyword in SQL Server

Note: If we are using where clause and order by clause in a single query then first where clause gets executed and then order by clause is gets executed.

How to Sort the Data in SQL Server by the Relative Position?

We can also use the SQL Server ORDER BY clause to sort by relative position in the result set, where the first field in the result is set to 1. The next field is 2, and so on. For better understanding, please have a look at the below example. Here, we specify the relative position as 1 in the order by clause.
SELECT Name, EmailID, Salary FROM Employee WHERE Salary > 26000 ORDER BY 1 DESC;
The above SQL Server ORDER BY statement would return all the records sorted by the Name field in the descending order whose Salary is greater than 26000. Since the Name field is in position 1 in the select clause and would be equivalent to the following ORDER BY clause example.
SELECT Name, EmailID, Salary FROM Employee WHERE Salary > 26000 ORDER BY Name DESC;
When you execute the above SQL query, you will get the below output.

How to Sort the Data in SQL Server by the Relative Position

How to use both ASC and DESC attributes in a Single Select Statement in SQL Server?

When sorting the result set using the SQL Server ORDER BY clause, we can also use the ASC and DESC attributes in a single SELECT statement. In the following query, the Order By Clause will return all records sorted by the Gender field in descending order, with a secondary sort by Name field in ascending order whose salary is greater than 25000.

SELECT Name, Gender, EmailID, Salary
FROM Employee
WHERE Salary > 25000
ORDER BY Gender DESC, Name ASC;

When you execute the above query, you will get the below output.

How to use both ASC and DESC attributes in a Single Select Statement in SQL Server

Note: When we have multiple columns in order by clause, the data first gets arranged based on the first column and if any duplicate values are there in the first column then it will take the support of the second column for the arrangement or else the second column will not be used.

Understanding the OFFSET and FETCH options used in the Order By Clause in SQL Server:
OFFSET Option in SQL Server:

When we are using order by clause in a query if we want to eliminate the number of records (rows) from the starting record (TOP) then we need to use the OFFSET option along with the order by clause. For better understanding, please have a look at the below query. Here, first, it will sort the employees by name in ascending order and then skips the top five records from the result set and the rest will be returning as output.
SELECT * FROM Employee ORDER by Name ASC OFFSET 5 ROWS
When you execute the above query, you will get the following output. Notice, it will skip the first 5 records in the result set.

OFFSET options in Order By Clause in SQL Server

FETCH Option in SQL Server:

By using the FETCH option in SQL Server Order By Clause we can specify the number of rows to return after the offset clause is processed. For better understanding please have a look at the below query. Here, we are using OFFSET 3 ROWS FETCH NEXT 4 ROWS. It means once the data is sorted then skip 3 records from the top and then fetch 4 records only as part of the result set.
SELECT * FROM Employee ORDER by ID ASC OFFSET 3 ROWS FETCH NEXT 4 ROWS ONLY
Once you execute the above query, you will get the rows from 4 to 7 in the result set skipping the first 3 rows as shown in the below image.

FETCH options in Order By Clause in SQL Server

Example: OFFSET With 0

When we specify OFFSET as 0 means it will skip 0 records. For better understanding, please have a look at the below query. Here we specified OFFSET as 0 and FETCH as 5. Means skip 0 records and fetch 5 records.
SELECT * FROM Employee ORDER by ID ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
When you will execute the above query, you will get the first 5 records of the result set as shown in the below image.

Order By Clause in SQL Server with Examples

Example: FETCH With 0

We can never specify the FETCH value as 0. Because Fetch 0 means it will return 0 records which do not make any sense. For better understanding, please have a look at the below example. Here, we specify the FETCH as 0.
SELECT * FROM Employee ORDER by ID ASC OFFSET 5 ROWS FETCH NEXT 0 ROWS ONLY
When you execute the above query, it will give the error as The number of rows provided for a FETCH clause must be greater then zero.

In the next article, I am going to discuss the TOP N Clause in SQL Server with examples. Here, in this article, I try to explain Order By clause in SQL Server with Examples. I hope this Order By clause in SQL Server with Examples article will help you with your needs.