[RESOLVED] Sort Select Distinct with NULLs last

[mataichi]

Hi,
I am trying to select some data from a table and sort by a column so that if it's value is NULL, then it will come last instead of first (sql default).
So googling provided me with this option....
SELECT Col1,Col2,Col3 FROM Table1 ORDER BY CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END,Col1

This does exactly what I want it to do by putting the NULL values ordered last.

However, when I try to select distinct values it breaks and tells me "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.", even when I put each column in the order by clause.
SELECT DISTINCT Col1,Col2,Col3 FROM Table1 ORDER BY CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END,Col1,Col2,Col3

I'm not smart enough to understand why distinct makes this break. Does anybody have a solution so that I can SELECT DISTINCT and order so that a columns null values will be ordered last? I am programming in ASP.

Thanks

[chazzy]

You need to have the case statement referenced somewhere. put it in your select, then reference that alias in your order by

[mataichi]

Worked perfectly! You are the man. A million thanks.