Sorting mixed data correctly. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sorting mixed data correctly.

Author

Topic

wisdomt
Starting Member

5 Posts

Posted - 2009-07-10 : 04:49:19

Hi,

I have a alphanumeric field that contains a mix of numeric and alphanumeric data. Sorting by the field produces output like:

0, 1, 10, 100, 2, 20, DES-01, DES-02

however I want the output as:

0, 1, 2, 10, 20, 100, DES-01, DES-02

I can see how to do this if there was no alphanumeric data but not when there is.

My SQL is currently:

SELECT DISTINCT BoxNo FROM vArchive WHERE NOT BoxNo='' ORDER BY BoxNo

Thanks in advance for your help.

Regards,

Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 05:16:15

SELECT DISTINCT BoxNo FROM vArchive
ORDER BY LEN(BoxNo), BoxNo

N 56°04'39.26"
E 12°55'05.63"

wisdomt
Starting Member

5 Posts

Posted - 2009-07-10 : 05:29:00

Thanks Peso,

I get the following error message when I add LEN(BoxNo) to the ORDERBY.

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

If I take out the DISTINCT the sort works but I get hundreds of duplicated entries in the list.

wisdomt
Starting Member

5 Posts

Posted - 2009-07-10 : 05:34:22

Worked it out.

Duh...include it the select statement.

SELECT DISTINCT LEN(BoxNo), BoxNo FROM vArchive
WHERE NOT BoxNo=''
ORDER BY LEN(BoxNo), BoxNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources