Help required for order by clause

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 required for order by clause

Author

Topic

abhiroop
Starting Member

2 Posts

Posted - 2009-01-07 : 06:52:22

I have a select query returning a set of strings of the format
ABC-XYZ-SrNo(3digit)-YYYY eg ABC-XYZ-101-2008, ABC-XYZ-011-2009, ABC-XYZ-058-2008 called RAFNo.
This strings needs to be arranged descending order with YYYY-SeialNo part in the order by clause. as in ABC-XYZ-011-2009 then ABC-XYZ-058-2008 then ABC-XYZ-101-2008
Can anyone help me with the query.
select RAFNo
from parseddata
order by ___________________

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-07 : 06:57:13

declare @table table(str varchar(66))
insert into @table
select 'ABC-XYZ-058-2008' union all
select 'ABC-XYZ-011-2009' union all
select 'ABC-XYZ-101-2008'
select * from @table order by str

o/p is

ABC-XYZ-011-2009
ABC-XYZ-058-2008
ABC-XYZ-101-2008
Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-07 : 07:06:11

Jai krishna's solution is correct, also use group by instead of order by for ur expected output

declare @table table(str varchar(66))
insert into @table
select 'ABC-XYZ-058-2008' union all
select 'ABC-XYZ-011-2009' union all
select 'ABC-XYZ-101-2008'

select * from @table group by str

output:-

ABC-XYZ-011-2009
ABC-XYZ-058-2008
ABC-XYZ-101-2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 07:08:32

Why would you use GROUP BY to ensure order of record output?
If you have more than one page, a page split may break your logic.

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-07 : 07:10:28

Oh ur correct Thanks Peso

abhiroop
Starting Member

2 Posts

Posted - 2009-01-07 : 07:26:18

the strings are the values of a column of a table and I want to order by YYYY-SrNo value part of the string so without taking into consideration ABC-XYZ part values which may not be same.

so the output should be of the type

bbb-aaa-011-2009
aaa-bbb-007-2009
xxx-www-099-2008
qqq-rrr-090-2008
fff-ggg-080-2007

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 07:32:43

quote:
Originally posted by abhiroop

the strings are the values of a column of a table and I want to order by YYYY-SrNo value part of the string so without taking into consideration ABC-XYZ part values which may not be same.

so the output should be of the type

bbb-aaa-011-2009
aaa-bbb-007-2009
xxx-www-099-2008
qqq-rrr-090-2008
fff-ggg-080-2007

use

order by right(columnname,8)

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 07:33:10

try this

declare @table table(string varchar(66))
insert into @table
select 'ABC-XYZ-058-2008' union all
select 'ABC-XYZ-011-2009' union all
select 'ABC-XYZ-101-2008'
select * from @table order by substring(string,9,len(string)) desc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 07:34:06

[code]order by right(columnname,8) desc[/code]
going by your output.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-07 : 07:35:06

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-07 : 07:37:23

declare @table table(str varchar(66))
insert into @table
select 'bbb-aaa-011-2009' union all
select 'aaa-bbb-007-2009' union all
select 'xxx-www-099-2008' union all
select 'qqq-rrr-090-2008' union all
select 'fff-ggg-080-2007'

select * from @table order by right(str,4) desc,substring(str,9,3) desc

Jai Krishna

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 07:40:16

quote:
Originally posted by Nageswar9

declare @table table(str varchar(66))
insert into @table
select 'ABC-XYZ-058-2008' union all
select 'ABC-XYZ-011-2009' union all
select 'ABC-XYZ-101-2008'

select * from @table order by right(str,9)desc

order by right(str,8) desc is enough as sakets_2000 suggested..isn't it ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources