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

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
CREATE TABLE tbl_Department
DeptAddress VARCHAR(512),
DeptContact VARCHAR(15)

Now, add some sample rows into the table:

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

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

Sample table

Sample table

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:

Desired output

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
(CASE WHEN DeptName = 'Not Assigned' THEN 0 ELSE 1 END), DeptName

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.

Rate This
[Total: 6    Average: 4/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

Leave a comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 thoughts on “Specific row at the top then sort the rest result set in SQL Server