Interview Question - How to Conditionally Sort the Records? TIP# 43

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