Order By String as Number - 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

Order By String as Number

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-31 : 06:19:53

Hi I have

select distinct cust_disc_cat from tblDiscounts_GoldDistributors

Id like to order by this columns as INT but its a VARCHAR

so i get

1
11
2

etc

id like

1
2
11

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-31 : 06:26:03

select distinct cust_disc_cat from tblDiscounts_GoldDistributors
order by cast(id as int)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-31 : 06:37:54

I tried this

SELECT DISTINCT CUST_DISC_CAT
FROM tblDiscounts_GoldDistributors
ORDER BY CAST(CUST_DISC_CAT AS int)

But got error order by item must appear in select list

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-31 : 06:53:17

try this



SELECT T.CUST_DISC_CAT
FROM 
	(SELECT DISTINCT CUST_DISC_CAT
	FROM tblDiscounts_GoldDistributors
	) T
ORDER BY CAST(CUST_DISC_CAT AS int)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-31 : 06:55:26

select * from(
select distinct CUST_DISC_CAT from tblDiscounts_GoldDistributors ) as a order by cast(CUST_DISC_CAT as int)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-31 : 06:59:29

Use Group by, Better performance then above

select CUST_DISC_CAT from tblDiscounts_GoldDistributors
group by CUST_DISC_CAT order by cast(CUST_DISC_CAT as int)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Velnias
Yak Posting Veteran

58 Posts

Posted - 2010-03-31 : 07:04:31

That did the trick cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-31 : 08:09:53

<<
Id like to order by this columns as INT but its a VARCHAR
>>

If you are going to store only numbers, better you change the datatype to INT
Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail

PackRat
Starting Member

26 Posts

Posted - 2010-03-31 : 10:53:25

quote:
Originally posted by madhivanan

<<
Id like to order by this columns as INT but its a VARCHAR
>>

If you are going to store only numbers, better you change the datatype to INT
Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail

...and if for some reason you can't switch it to a numeric data type consider adding something like

WHERE ISNUMERIC([col])=1

to ensure that you don't error out if there is string data intermingled

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 11:01:22

quote:
Originally posted by PackRat

quote:
Originally posted by madhivanan

<<
Id like to order by this columns as INT but its a VARCHAR
>>

If you are going to store only numbers, better you change the datatype to INT
Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail

...and if for some reason you can't switch it to a numeric data type consider adding something like

WHERE ISNUMERIC([col])=1

to ensure that you don't error out if there is string data intermingled

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

ISNUMERIC() is not safe. See this article: [url]http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html[/url]

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources