select distinct and order by w/ case

[Database Administrator]

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?

[Database Administrator]

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


wrote in message
news:50903cad-ec43-4f58-8f50-65b2c54c0075@y22g2000prd.googlegroups.com...
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?

[Database Administrator]

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

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;

[Database Administrator]

> SELECT DISTINCT customer_id, contact_name,
> CASE WHEN @my_sort = 1 THEN customer_id
> THEN contact_name END AS sorted_by

I think you meant:

ELSE contact_name END AS sorted_by

> FROM Customers
> ORDER BY sorted_by ASC;

[Database Administrator]

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!


bryanp10@hotmail.com wrote:
> 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?

[shijobaby]

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009...select_19.html