Specific row at the top then sort the rest result set in SQL Server

In this post, “Specific row at the top then sort the rest result set in SQL Server“, we are going to learn that how we can order a result set in a customized way which cannot be achieved using ORDER by Clause in a simple way.

To demonstrate this, let’s create a sample table named as “tbl_Department” and insert some dummy rows in it. Below is the code to create the sample table:

IF(OBJECT_ID('dbo.tbl_Department') IS NOT NULL)
DROP TABLE tbl_Department
GO
CREATE TABLE tbl_Department
(
DeptId INT IDENTITY(1,1) NOT NULL,
DeptName VARCHAR(256) NOT NULL,
DeptAddress VARCHAR(512),
DeptContact VARCHAR(15)
)
GO

Now, add some sample rows into the table:

INSERT INTO tbl_Department
(DeptName, DeptAddress, DeptContact)
VALUES
('Engineering', 'New Delhi', '1234567890'),
('Human Resources', 'Chennai', '9876543210'),
('Admin', 'Kolkata', '2763282789'),
('Human Resources', 'New Delhi', '1928272727'),
('Not Assigned', 'Mumbai', '1839288976')
GO

Let’s have a look at the table data now:

From this table, we need to extract all rows and columns by ordering them on DeptName column. However, we need to keep “Not Assigned” department name value at the top of the list and then the rest of the department name values should be ordered alphabetically in ascending order. Have a look at the desired output:

How to keep specific row at the top then sort the rest result set in SQL Server

Now, we will be discussing the method which we are going to use to keep a specific record at the top of the list and then sort the rest of the list by a given column name. Let’s discuss the method.

Using CASE expression with ORDER BY clause

In this method, we will be using a CASE expression in order by clause which will keep the “Not Assigned” department name at the top of the list. Below is the code:

SELECT * FROM tbl_Department
ORDER BY
(CASE WHEN DeptName = 'Not Assigned' THEN 0 ELSE 1 END), DeptName
GO

In above code, we are using “(CASE WHEN DeptName = ‘Not Assigned’ THEN 0 ELSE 1 END)” case expression which keeps the “Not Assigned” department name always at top of the list. After that, we have used “DeptName” column to sort the rest of the values in ascending order alphabetically.

Using this trick, we can keep a given row at the top of the select list and then we can use other column names to sort the result sets as needed.

Thanks for the reading. Please share your input in the comment section of this post and share this post if you like this.