Help with Order By - 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 2005 Forums

Transact-SQL (2005)

Help with Order By

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-04-04 : 10:50:33

Hi - I've a query that is doing a few selects and inserting into temp tables, one of the columns in 1 of these temp tables contains data in this format Week9, Week10, Week11, Week12, Week13 (depending on which week it is from the start of the yr).
My problem begins when trying to sort the data in the final select:
1) if I use 'Order by RS.EffWeek DESC' I get my results ordered in this order Week9-Week13-Week12-Week11-Week10
2) if I don't use any ordering the results are ordered: Week10-Week11-Week12-Week13-Week9

Can anyone help me on sorting the results sequentially i.e. Week9-Week10-Week11-Week12-Week13

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-04 : 10:58:05

you need to normalize it in some fashion... if it will always start with 'Week'...:


Declare @t table (
	d varchar(100)
)

Insert Into @t Select 'Week9'
Insert Into @t Select 'Week10'
Insert Into @t Select 'Week11'
Insert Into @t Select 'Week12'
Insert Into @t Select 'Week13'
Insert Into @t Select 'Week102'


Select * 
From @t 
Order By 1


Select * 
From @t 
Order By convert(int,replace(d,'Week',''))

Corey

I Has Returned!!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-04-04 : 10:58:20

ORDER BY CONVERT(int, REPLACE(RS.EffWeek, 'Week', ''))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-05 : 07:06:03

or

Select *
From @t
Order By LEN(d),d

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 07:32:38

booooo.....

I don't like this one... it's to nebulous (sp?)

quote:
Originally posted by madhivanan

or

Select *
From @t
Order By LEN(d),d

Madhivanan

Failing to plan is Planning to fail

Corey

I Has Returned!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 08:13:24

quote:
Originally posted by Seventhnight

booooo.....

I don't like this one... it's to nebulous (sp?)

Corey

I Has Returned!!

Heh! Your post made me chuckle :--) (And so did your signature line!!)

I wonder why Madhivanan who is consistently very un-nebulous decided to go nebulous on this one :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 08:25:33

Oh who knows... maybe he's just trying to be different

...

After all, it is a valid option in this scenario.

Corey

I Has Returned!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-05 : 10:05:40

quote:
Originally posted by Seventhnight

booooo.....

I don't like this one... it's to nebulous (sp?)

quote:
Originally posted by madhivanan

or

Select *
From @t
Order By LEN(d),d

Madhivanan

Failing to plan is Planning to fail

Corey

I Has Returned!!

As long as the string "week" is fixed, there is nothing wrong with the query. Also if the pure numbers are stored in the varchar column, you can use the above method too

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 11:05:32

quote:
Originally posted by madhivanan

As long as the string "week" is fixed, there is nothing wrong with the query. Also if the pure numbers are stored in the varchar column, you can use the above method too

All true,especially given the data that you have to work with.

But it is fun to pretend that you are being nebulous because it is not very often you even go near nebulosity (except for your affection for parsename, which I am not very fond of!)

PS: I wonder if "nebulosity" is a word

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 11:11:19

Oh, pish posh. I never said your query was incorrect or invalid... just that i don't like it.

It's too generic for my tastes.

Of course, you won't catch me with a column with data like that. I would have atleast padded the numbers so they were all the same length.

quote:
Originally posted by madhivanan

quote:
Originally posted by Seventhnight

booooo.....

I don't like this one... it's to nebulous (sp?)

quote:
Originally posted by madhivanan

or

Select *
From @t
Order By LEN(d),d

Madhivanan

Failing to plan is Planning to fail

Corey

I Has Returned!!

As long as the string "week" is fixed, there is nothing wrong with the query. Also if the pure numbers are stored in the varchar column, you can use the above method too

Madhivanan

Failing to plan is Planning to fail

Corey

I Has Returned!!

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-04-06 : 06:25:06

Seventhnight if the value in the field ranges from Week1 > Week52, is there anyway to insert a 0 (into position 5) if the length of the field <> 6?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 08:27:54

Sure... stuff() or replace()

Here are a couple options... I'm sure there are more ways to do it


	
Select 
	d,
	d1 = case when len(d)=5 then stuff(d,5,0,'0') else d end,
	d2 = case when len(d)=5 then replace(d,'k','k0') else d end,
	d3 = 'Week'+right(replace(d,'Week','00'),2)
From
	(
	Select 
		d='Week'+convert(varchar,number)
	From master..spt_values 
	Where type = 'P'
	and number between 1 and 52
	) A

Corey

I Has Returned!!

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-04-06 : 10:10:21

good job Seventhnight, thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources