CODEWITHSUNDEEP
sql-serversql-server-2008subquerysql-order-by
Nicklas
Nicklas

Reputation: 23

ORDER BY CASE with a subquery

I have a problem to order by a dynamic value when I use AVG or COUNT with SQL Server. How can I make it work?

ALTER PROCEDURE GetEmployee
@SortOrder INT
AS

SELECT          name, 
                AVG(salary) AS avgSalary,               
                COUNT(employeeID) AS employeeCount              

FROM            employee

ORDER BY CASE WHEN @SortOrder = 1 THEN avgSalary
              WHEN @SortOrder = 2 THEN employeeCount
              ELSE name
         END

Upvotes: 2

Views: 206

Answers (1)

ta.speot.is
ta.speot.is

Reputation: 27214

This will probably work, and is basically a quick workaround for the fact that all expressions in a CASE statement should be coerce-able to the same time.

ALTER PROCEDURE GetEmployee
@SortOrder INT
AS

SELECT          name, 
                AVG(salary) AS avgSalary,               
                COUNT(employeeID) AS employeeCount              

FROM            employee

ORDER BY CASE WHEN @SortOrder = 1 THEN AVG(salary) END,
         CASE WHEN @SortOrder = 2 THEN COUNT(employeeID) END,
         CASE WHEN @SortOrder NOT IN (1, 2) THEN name END

Upvotes: 1

Related Questions