SQL Syntax for distinct select

[blue]

I'm trying to order a varchar column first numerically, and second
alphanumericall y using the following SQL:

SELECT distinct doc_number
FROM doc_line
WHERE product_id = 'WD' AND doc_type = 'O'
ORDER BY CASE WHEN IsNumeric(doc_n umber) = 1
THEN CONVERT(FLOAT, doc_number)
ELSE 999999999
END,
CASE WHEN IsNumeric(doc_n umber) = 1
THEN 'ZZZZZZZZZ'
ELSE doc_number
END;

When try executing this statement, I get the following error:

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.

If I take the "distinct" out, it works just fine, except for the fact that I
get many duplicates.

Does anyone have any suggestions?

Thanks,
Frank

[Erland Sommarskog]

Re: SQL Syntax for distinct select

[posted and mailed, please reply in news]

blue (fluyckx@config sc.com) writes:[color=blue]
> I'm trying to order a varchar column first numerically, and second
> alphanumericall y using the following SQL:
>
> SELECT distinct doc_number
> FROM doc_line
> WHERE product_id = 'WD' AND doc_type = 'O'
> ORDER BY CASE WHEN IsNumeric(doc_n umber) = 1
> THEN CONVERT(FLOAT, doc_number)
> ELSE 999999999
> END,
> CASE WHEN IsNumeric(doc_n umber) = 1
> THEN 'ZZZZZZZZZ'
> ELSE doc_number
> END;
>
> When try executing this statement, I get the following error:
>
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.[/color]

Try this:

SELECT doc_number
FROM (SELECT DISTINCT doc_number
FROM doc_line
WHERE product_id = 'WD'
AND doc_type = 'O') AS x
ORDER BY CASE WHEN doc_number NOT LIKE '%[0-9]%'
THEN CONVERT(FLOAT, doc_number)
ELSE 999999999
END,
CASE WHEN doc_number NOT LIKE '%[0-9]%'
THEN 'ZZZZZZZZZZ'
ELSE doc_number
END

I am using here a derived table, which logically is a temp table
created on the fly. However, the optimizer may pick a plan that
evades the table from actualy being created. This is a very
powerful SQL construct.

I also replaced your use of isnumeric, since this function is very
unreliable. It returns 1 if the string can be converted to some
numeric datatype, but you don't know which. For instance, a string
like '.' is numeric, but you cannot convert it to float. (But you
can convert it to money.) I assumed that you are only looking for
integer numbers. You don't have doc numbers like 1E234, have you?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[blue]

Re: SQL Syntax for distinct select

Erland,

Thank you for your suggestion. However, I did get an error trying to run
your sql:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

When using the isNumeric, everything looks as expected:

SELECT doc_number
FROM (SELECT DISTINCT doc_number
FROM doc_line
WHERE product_id = 'WD'
AND doc_type = 'O') AS x
ORDER BY CASE WHEN IsNumeric(doc_n umber) = 1
THEN CONVERT(FLOAT, doc_number)
ELSE 999999999
END, CASE WHEN IsNumeric(doc_n umber) = 1 THEN 'ZZZZZZZZZ' ELSE doc_number
END

To answer your question about the doc numbers, which are customer order
numbers, yes, they can have any alphanumeric combination, with no special
characters.

Again, thanks for your help. Regards:

Frank

"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns959CDB9 D0E13DYazorman@ 127.0.0.1...[color=blue]
> [posted and mailed, please reply in news]
>
> blue (fluyckx@config sc.com) writes:[color=green]
>> I'm trying to order a varchar column first numerically, and second
>> alphanumericall y using the following SQL:
>>
>> SELECT distinct doc_number
>> FROM doc_line
>> WHERE product_id = 'WD' AND doc_type = 'O'
>> ORDER BY CASE WHEN IsNumeric(doc_n umber) = 1
>> THEN CONVERT(FLOAT, doc_number)
>> ELSE 999999999
>> END,
>> CASE WHEN IsNumeric(doc_n umber) = 1
>> THEN 'ZZZZZZZZZ'
>> ELSE doc_number
>> END;
>>
>> When try executing this statement, I get the following error:
>>
>> Server: Msg 145, Level 15, State 1, Line 1
>> ORDER BY items must appear in the select list if SELECT DISTINCT is
>> specified.[/color]
>
> Try this:
>
> SELECT doc_number
> FROM (SELECT DISTINCT doc_number
> FROM doc_line
> WHERE product_id = 'WD'
> AND doc_type = 'O') AS x
> ORDER BY CASE WHEN doc_number NOT LIKE '%[0-9]%'
> THEN CONVERT(FLOAT, doc_number)
> ELSE 999999999
> END,
> CASE WHEN doc_number NOT LIKE '%[0-9]%'
> THEN 'ZZZZZZZZZZ'
> ELSE doc_number
> END
>
> I am using here a derived table, which logically is a temp table
> created on the fly. However, the optimizer may pick a plan that
> evades the table from actualy being created. This is a very
> powerful SQL construct.
>
> I also replaced your use of isnumeric, since this function is very
> unreliable. It returns 1 if the string can be converted to some
> numeric datatype, but you don't know which. For instance, a string
> like '.' is numeric, but you cannot convert it to float. (But you
> can convert it to money.) I assumed that you are only looking for
> integer numbers. You don't have doc numbers like 1E234, have you?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: SQL Syntax for distinct select

blue (fluyckx@config sc.com) writes:[color=blue]
> Thank you for your suggestion. However, I did get an error trying to run
> your sql:
>
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to float.[/color]

Ah, that LIKE expression is complicated, and I got it wrong:
[color=blue][color=green]
>> ORDER BY CASE WHEN doc_number NOT LIKE '%[0-9]%'[/color][/color]

It should be:

ORDER BY CASE WHEN doc_number NOT LIKE '%[^0-9]%'

Exercise: try to understand how it works!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp