select distinct and order by w/ case - Tom Moreau

A derived table can be used as a workaround:

select
*
from
(
SELECT DISTINCT CustomerID, ContactName FROM Customers
) as x
ORDER BY
CASE WHEN 1 = 1 THEN CustomerID END ASC,
CASE WHEN 0 = 1 THEN ContactName END ASC



--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


Can anyone tell me why this fails in Northwind?

SELECT DISTINCT CustomerID, ContactName FROM Customers
ORDER BY
CASE WHEN 1 = 1 THEN CustomerID END ASC,
CASE WHEN 0 = 1 THEN ContactName END ASC

I get the old "ORDER BY items must appear in the select list if SELECT
DISTINCT is specified"... But both sort columns appear in the select
list.

Without the CASE on the sort, it works fine of course.  What is it
about the CASE that throws off SQL Server?  Is this not possible or is
my syntax just incorrect here?

select distinct and order by w/ case - Aaron Bertrand [SQL Server MVP]

I think you meant:

ELSE contact_name END AS sorted_by

select distinct and order by w/ case - bryanp1

Can anyone tell me why this fails in Northwind?

SELECT DISTINCT CustomerID, ContactName FROM Customers
ORDER BY
CASE WHEN 1 = 1 THEN CustomerID END ASC,
CASE WHEN 0 = 1 THEN ContactName END ASC

I get the old "ORDER BY items must appear in the select list if SELECT
DISTINCT is specified"... But both sort columns appear in the select
list.

Without the CASE on the sort, it works fine of course.  What is it
about the CASE that throws off SQL Server?  Is this not possible or is
my syntax just incorrect here?

select distinct and order by w/ case - --CELKO--

No, they don't.  CASE is an expression and not a column in the SELECT
list.  In Standard SQL, the ORDER BY clause can only use the column
names or aliases.  SQL Server is based on contiguous physical storage,
and allows things in the ORDER BY clause that are illegal.  But with a
SELECT DISTINCT, it has to sort the result set to remove redundant
duplicates that working table follows ANSI/ISO rules.

What I would recommend is putting the CASE expression in the SELECT
list, giving it a name and then use that name in the ORDER BY.  This
will port to any SQL and you can actually see what you are sorting by
if you need to, something like this:

SELECT DISTINCT customer_id, contact_name,
CASE WHEN @my_sort = 1 THEN customer_id
THEN contact_name END AS sorted_by
FROM Customers
ORDER BY sorted_by ASC;

select distinct and order by w/ case - bryanp1

Awesome, thanks for the quick response, guys.  The derived table
method works great.

Celko, I understand now why this wasn't working.  Your suggestion has
a disadvantage though, in that I need multiple CASE statements in
order to sort on columns of different types.  For example, this fails:

SELECT Distinct OrderID, CustomerID, EmployeeID,
CASE WHEN 0 = 1 THEN OrderID
WHEN 2 = 2 THEN CustomerID
WHEN 0 = 3 THEN EmployeeID END
AS sorted_by
FROM Orders
ORDER BY sorted_by ASC



Appreciate the help!