NULL Values at Bottom of SQL Table – SQL Server 2000

I have a table with information containing names and emails. I have to put the rows containing NULL email values at the bottom of the table, but am unable to when I use the SELECT DISTINCT clause. Here is my current syntax:

SELECT 
DISTINCT
     user_index,
     name,
     email
FROM table1
ORDER BY (CASE WHEN email IS NULL THEN 1 ELSE 0 END)

​x
 
SELECT DISTINCT     user_index,     name,     emailFROM table1ORDER BY (CASE WHEN email IS NULL THEN 1 ELSE 0 END)​

but whenever I try to execute this query, I receive the error message:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

 
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.​

This query works if I take out the DISTINCT clause, but it is necessary in this case. Any tips?

Answer

Use aggregation instead:

SELECT user_index, name, email
FROM table1
GROUP BY user_index, name, email
ORDER BY (CASE WHEN email IS NULL THEN 1 ELSE 0 END);

 
SELECT user_index, name, emailFROM table1GROUP BY user_index, name, emailORDER BY (CASE WHEN email IS NULL THEN 1 ELSE 0 END);​

This is functionally the same as your question. However, the SQL engine will not get confused about an expression that uses a distinct column.

Advertisement

Answer