order clause to make Bubba-10 come after Bubba-9? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

order clause to make Bubba-10 come after Bubba-9?

Author

Topic

storemike
Starting Member

10 Posts

Posted - 2003-11-26 : 08:40:56

Hello,

I'm stumped. I've been searching for a way to do this but have had no luck. I have a field in my SQL Srvr 2K db called, say, FILENAME. When I "select FILENAME order by FILENAME asc", the results are as follows:

Bubba-1
Bubba-10
Bubba-2
Bubba-3
...

How do I get Bubba-10 to show up where it's supposed to? I tried several COLLATE clauses and event tried (why not, right?) the BINARY command.

Thanks tons!
Mike

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-26 : 08:44:45

you've got to look at putting "leading zero's" before the numbers...so that the numbers align up right for the sorting.

search here for "leading zero", and you'll see examples and solutions.

storemike
Starting Member

10 Posts

Posted - 2003-11-26 : 09:00:57

Andrew,

thanks for the reply. I got it to work using leading zeros if I didn't use an "order by" clause (even if I tried to order by the tmp val with the leading zero). This is what I came up with. It's probably a bit clunky, but it works for me.

select SUBSTRING(FILELABEL,0,CHARINDEX('-', FILELABEL)+1)+'0'+SUBSTRING(FILELABEL,CHARINDEX('-', FILELABEL)+1,(datalength(FILELABEL)-CHARINDEX('-', FILELABEL))),* from MYTABLE

This makes Bubba-1 into Bubba-01, Bubba-10 into Bubba-010. It works, but it doesn't make sense why 010 would come after 01, when 10 wouldn't come after 1.

Thanks again,
Mike

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-26 : 09:03:28

[code]
create table bubba ( val varchar(20))

insert into bubba
select 'Bubba-1'
union all select 'Bubba-10'
union all select 'Bubba-2'
union all select 'Bubba-3'

select val from bubba
order by convert(int,substring(val,charindex('-',val)+1,len(val)))

drop table bubba
[/code]

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-11-26 : 09:04:15

OK Bubba-01 comes before Bubba-010, but does Bubba-02 come before Bubba-010?

ehorn's should work.

storemike
Starting Member

10 Posts

Posted - 2003-11-26 : 09:18:32

Actually, I mis-typed. Bubba-10 was coming before Bubba-2. Sorry about that. ehorn's sql would work, but a few of these values are like "Bubba-T2", so the T2 part can't be converted to an int. The looong, probably over-complicated sql I have above puts these first in the list:

Bubba-0T2
Bubba-01
Bubba-02
Bubba-010
...

And that's fine. I want those first anyway.

Thanks for the help.
Mike

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-26 : 09:57:11

select right(replicate('0',6) + convert(varchar,mycode),6)
from mytable
order by right(replicate('0',6) + convert(varchar,mycode),6)

This should get you sorted properly.....the key is to get all the numbers aligned to the right, and with the same number of digits (including leading zeros).

This will then allow the sorting to be done properly. If you are NOT inluding an ODER BY in your SQL Statement...then you CANNOT guarantee the order of the results.

What you are sorting is TEXT that LOOKS LIKE numbers, rather than what you see & understand to be numbers....the leading zeros's matter.

storemike
Starting Member

10 Posts

Posted - 2003-11-26 : 10:10:54

That's works great but, now Bubba-T1 comes between Bubba-10 and Bubba-10A. The FILELABEL possiblilties can be pure numerical at the end (Bubba-10, Bubba-20, Bubba-3, Joe-23 etc) or a combo (Bubba-T1, Bubba-10A, Stan-T2 etc). I don't care if T1 values come first or last in the list as long as the numbers are in order.

If this is too much of a pain, I'll just give up and add a column for sort order.

Thanks,
Mike

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-26 : 10:36:24

[CODE]
create table bubba ( val varchar(20))

insert into bubba
select 'Bubba-10'
union all select 'Bubba-20'
union all select 'Bubba-3'
union all select 'Joe-23'

union all select 'Bubba-T1'
union all select 'Bubba-10A'
union all select 'Stan-T2'

SELECT val
FROM BUBBA
ORDER BY
CASE
WHEN ISNUMERIC(LEFT(REVERSE(VAL),1)) <> 1 THEN CONVERT(INT,REVERSE(SUBSTRING(REVERSE(VAL),2,CHARINDEX('-',REVERSE(VAL))-2)))
WHEN ISNUMERIC(LEFT(SUBSTRING(val,CHARINDEX('-',val)+1,LEN(val)),1)) <> 1 THEN CONVERT(INT,substring(val,CHARINDEX('-',val)+2,LEN(val)))
ELSE
CONVERT(INT,SUBSTRING(val,CHARINDEX('-',val)+1,LEN(val)))
END

drop table bubba
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources