sort vachar datatype - 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

SQL Server 2000 Forums

Transact-SQL (2000)

sort vachar datatype

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-12-04 : 18:43:42

There is a column "id_number" and is of type varchar(10). I want to sort the data based on id_number. I know if I use order by on id_number it doesnt work correctly. I cant use cast because some of the values are 'Blank'. Here is the sample data.

id_number:
Blank
456
123
978
Blank

I want results like this:
Blank
Blank
123
456
978

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-04 : 18:59:24

SELECT id_number
FROM YourTable
ORDER BY CASE WHEN id_number = 'Blank' THEN 1 ELSE 2 END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cognos79
Posting Yak Master

241 Posts

Posted - 2007-12-04 : 20:55:12

could you explain how order by 1 and 2 works....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-04 : 22:11:59

It is ordering by numbers now, 1 and 2.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 02:11:57

And the ORDER BY should be:

ORDER BY CASE WHEN id_number = 'Blank' THEN 1 ELSE 2 END, id_number

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 04:18:20

or

ORDER BY isnumeric(id_number),id_number

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 04:31:10

[code]DECLARE @Sample TABLE (Data VARCHAR(20))

INSERT @Sample
SELECT 'Blank' UNION ALL
SELECT '456' UNION ALL
SELECT '123' UNION ALL
SELECT '978' UNION ALL
SELECT 'Blank'

SELECT Data
FROM @Sample
ORDER BY NULLIF(Data, 'Blank'),
LEN(Data),
Data[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 07:23:33

SELECT		Data
FROM		@Sample
ORDER BY	CAST(NULLIF(Data, 'Blank') AS BIGINT)

SELECT		Data
FROM		@Sample
ORDER BY	CAST(CASE WHEN Data='Blank' THEN 0 ELSE data END AS BIGINT)

Provided the column has only numbers other than 'Blank'

Madhivanan

Failing to plan is Planning to fail

cognos79
Posting Yak Master

241 Posts

Posted - 2007-12-05 : 10:16:48

Thanks guys...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources