"Order By" issue

Question

Post reply

"Order By" issue

shyam.oec

Old Hand

Points: 380
More actions
June 6, 2010 at 4:37 am

#233452

Hi,
i have a table with one column of type varchar. Values inserted in it are '12th','9th','10th','11th','Dropped' in the same order. i want to select these values in order like this 9th,10th,11th,12th,Dropped. But simple order by clause is not giving right output. So, how to query for this ?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Assuming that there are values such as 1st, 2nd, 3rd, 4th and so on in there as well as the ones you've listed.

Use REPLACE to get rid of the suffixes, cast to int and order by that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dave-dj SSChampion Points: 11297 More actions · Answer 2

also you may need to check your collation

http://msdn.microsoft.com/en-us/library/aa174903(SQL.80).aspx

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

GK Kondamudi Grasshopper Points: 23 More actions · Answer 3

try

ORDER BY CAST(SUBSTRING(DATA+CHAR(32),1, PATINDEX('%[A-Z]%',DATA+CHAR(32))-1 ) AS INT)

where data is the column containing data like 12th, 9th, 3rd etc.

arun.sas SSChampion Points: 11831 More actions · Answer 4

But simple order by clause is not giving right output. So, how to query for this ?

declare @temp table (col1 varchar(10))

insert into @temp

select '1st' union all

select '15th' union all

select '3rd' union all

select '7th' union all

select '2nd' union all

select 'Dropped' union all

select '11th' union all

select '100th' union all

select '99th' union all

select '77th' union all

select '5th'

select * from @temp

ORDER BY len(col1),left(col1,1),left(col1,2)

sonaltotla Valued Member Points: 73 More actions · Answer 5

HI Try this

with CTEOrder as

(

select Rank,

Case Right(Rank,2)

when 'th' then

convert(int,Replace(Rank,'th',0))

when 'st' then

convert(int,Replace(Rank,'st',0))

when 'nd' then

convert(int,Replace(Rank,'nd',0))

when 'rd' then

convert(int,Replace(Rank,'rd',0))

else ''

end

A from OrderBy

)

select Rank from CTEOrder order by A