Order By Clause in SQL Server

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 some 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 Argument 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 attribute 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.

Syntax:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ASC | DESC];
Parameters or Arguments used with Order By Clause:
  1. Expressions: The columns or calculations that we want to retrieve.
  2. TablesThe 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 that 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:

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 in temporary but not in the permanent store.

Note: The Order By Clause can only be used in Select Statements.

Example:

Please use the 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

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;

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;

Sorting the records without using ASC/DESC attribute in SQL Server

Sorting in Descending Order using Order By Desc:

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;

Sorting in Descending Order using Order By Desc

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.

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;

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 our result set using the SQL Server ORDER BY clause, we can 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;

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.

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.

Understanding the OFFSET And FETCH Options used in the 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.

Understanding the OFFSET And FETCH Options used in the Order By Clause

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.

Understanding the OFFSET And FETCH Options used in the Order By Clause

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 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 some example. Here, in this article, I try to explain Order By clause in SQL Server with some simple examples. I hope this article will help you with your need. 

Leave a Reply

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