Problem
Can we sort records according to a particular condition?
Solution
Most of the time, developer faces this challenge of sorting records conditionally. I know many of us faces this question in interview.
Let's understand this by an example.
Suppose I have a person
table in database and ' I want to sort the records while fetching from database. Records sorting depends on a variable which is passed by the consumer from front end.
So if Sort variable 1, then we have to sort the records by First Name.
If Sort variable is 2, then we have to sort the records by Last Name.
Else we have to sort by Middle Name.
To achieve this, I have created the following stored procedure.
CREATE PROCEDURE proc_SortPerson
@SortBy TINYINT – if one then sort by first name if 2 sort by last name else sory by middlename
AS
BEGIN
SELECT *
FROM [Person].[Person] WITH(NOLOCK)
ORDER BY (CASE @SortBy WHEN 1 THEN FirstName
WHEN 2 THEN LastName
ELSE MiddleName
END)
END
GO
Now when I execute this by specific parameter, result is sorted according to that variable value.
See the below snapshots for proof of concept.
Sort by Last Name when @sortby
value = 2
.
You can add any condition according to your business need.
Thanks
Filed under: CodeProject, DENALI, Sql server, SQL SERVER 2008 - R2, SQL SERVER 2014, TIPS
Tagged: case in order by, conditional Order by
I am Rajat Jaiswal from India. I am working as a Technology specialist in one of the reputed company in India with 12+ years of experience. I am a Microsoft Data Platform MVP & Alibaba Cloud MVP.
I have always had an affinity to learn something new in technology. Database, Web development, Cloud computing are the areas of my interests always.
I have been a regular contributor to technologies with more than 300+ tips. Learning & Sharing is one of my aims always. Writing blogs, delivering sessions, helping on forums is my hobby.