Order By Clause in SQL Server

Order By Clause in SQL Server

In this article, I am going to discuss the Order By Clause in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed the where clause in SQL Server with some examples. 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.

Syntax

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ASC | DESC];
Parameters or Arguments

ExpressionsThe columns or calculations that we want to retrieve.

TablesThe tables from which we want to retrieve the records. There should be at least one table specified in the FROM clause.

WHERE conditions: It is optional. The conditions that must be met for the records to be selected by the query.

ASC: It is optional. If you want to sort the result set in ascending order of the expression then you need to use ASC.  

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:

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 in SQL Server can be applied to any data type column in the table. The Order By Clause in SQL Server will arrange the data in temporary but not in the permanent store.

Note: The Order By Clause can only be used in SELECT statements.

Use below script to create and populate the Employee table with test 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

Fetch all the records from Employee table

SELECT * FROM Employee

Order By Clause in SQL Server

Example – Sorting without using ASC/DESC attribute

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;

This 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:

SELECT *
FROM Employee
ORDER BY Name ASC;

Order By Clause in SQL Server asc

Example – Sorting in descending order

When we want to sort our result set in descending order then we need to use the DESC attribute in our 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;

Order By Clause in SQL Server desc

Example – Sorting by 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.

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 result set and would be equivalent to the following ORDER BY clause:

SELECT Name, EmailID, Salary
FROM Employee
WHERE Salary > 26000
ORDER BY Name DESC;

Order By Clause in SQL Server relative position

Example – Using both ASC and DESC attributes

When sorting our result set using the SQL Server ORDER BY clause, we can use the ASC and DESC attributes in a single SELECT statement.

The below SQL Server 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;

Order By Clause in SQL Server both asc and desc

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 arrangement or else the second column will not be used.

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.

OFFSET And FETCH options under order by Clause:

OFFSET:

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 OFFSET option along with order by clause.

Example:

SELECT *
FROM Employee
ORDER by Name ASC OFFSET 5 ROWS

It will skip the first 5 records in the result as shown below.

Order By Clause in SQL Server OFFSET

FETCH:

By using FETCH option we can specify the number of rows to return after the offset clause is processed.

Example:

SELECT *
FROM Employee
ORDER by ID ASC OFFSET 3 ROWS FETCH NEXT 4 ROWS ONLY

Gets the rows from 4 to 7 in the result set skipping the first 3 rows as shown below.

Order By Clause in SQL Server FETCH

Example: OFFSET With 0

SELECT *
FROM Employee
ORDER by ID ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

Gets the first 5 rows of the result set as shown below.

Order By Clause in SQL Server OFFSET with 0

Example: FETCH With 0

We can never specify the FETCH value as 0.

SELECT *
FROM Employee
ORDER by ID ASC OFFSET 5 ROWS FETCH NEXT 0 ROWS ONLY

It will give the below error

The number of rows provided for a FETCH clause must be greater then zero.

In the next article, I will discuss the TOP N Clause in SQL Server.

SUMMARY

In this article, I try to explain Order by clause in SQL Server steps by step with some simple examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *