Can we sort records according to particular condition ?
Most of the time developer faces this challenge of sorting records conditionally. I know many of us faces this question in interview.
Lets 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 is depended 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 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
FROM [Person].[Person] WITH(NOLOCK)
ORDER BY (CASE @SortBy WHEN 1 THEN FirstName
WHEN 2 THEN LastName
Now when I execute this by specific parameter result sorted according to that variable value.
See below snaps for proof of concept
Sort by Last Name when @sortby value = 2
You can add any condition according to your business need.