Interview Question- How conditionally sort the records ? TIP#43

 

Problem:-

Can we sort records according to 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.

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
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 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.

Thanks

Rj !!