hard sort - 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 2000 Forums

Transact-SQL (2000)

hard sort

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-19 : 10:26:07

hi guys and gals!

i need to sort the regions in the order of their prefixes that is given. i've put it in a CSV but it can be in some other form...
the problem is that the prefix is not fixed length and delimiter after it can be anything.


declare @sortOrder varchar(500)
set @sortOrder = 'TB,KD,HV'

declare @MyTable table(RegionName Varchar(100))
insert into @MyTable
select 'HV-Region12' union all
select 'HV-Region2' union all
select 'KD-Region5' union all
select 'KD-Region4' union all
select 'KD-Region32' union all
select 'No region' union all
select 'PK-Region blah' union all
select 'RVL-Region ghf' union all
select 'TB-Region geet' union all
select 'USA some Group'

select * from @MyTable

resultset:
'TB-Region geet'
'KD-Region4'
'KD-Region5'
'KD-Region32'
'HV-Region2'  
'HV-Region12'
all others in no specific order.

it was a long day already and simply nothing slick comes to mind.
Help!

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-19 : 10:39:34

ok i've come up with this:


set @sortOrder = ',HV,KD,TB,' -- notice the reversed order that before, that's because of DESC below

select *
from @MyTable
order by charindex(','+left(RegionName, patindex('%[^a-zA-Z]%', RegionName)-1)+',', @sortOrder) DESC

can any one come up with something better and faster??

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-19 : 12:36:18

Nice one Spirit!

One day that will become some really cool legacy code to support

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-19 : 13:30:26

heh...

that's why i'd like something better.
good thing is there won't be more that 100 rows of this.
any suggestions??

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-19 : 13:44:03

Create a table with the sortstrings and a sorting column.
Join the table + sorting table (left join) and sort by the sorting column.
It's more work, but easier to read I guess.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-01-20 : 01:22:40

I'd have a separate table too (or SPLIT on the comma to a temporary table) with SortMatch and Rank columns

Then I would LEFT JOIN #MyTemp ON Region LIKE SortMatch + '%' ORDER BY Rank, Region

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-20 : 03:22:53

It's always reassuring with a second opinion

.

'KD-Region32' looks like code + text concatenated.
Couldn't you just have select code, code+'-'+text ... from ... order by ...
without the patindex/like/charindex stuff.

The client will decide which columns to dsiplay.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-20 : 03:23:41

Oooops #900.
Just slipped in.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-20 : 06:22:08

rockmoose only 100 to go. about 2 weeks, huh?

yeah temp table was also my first guess, but as this is done with sql in the app (no sprocs)
I didn't feel like writing the temp table and inserts in VBScript... more work like you said.

region is in all in one column and it can be with or without prefix. cool design, huh?
well in the end the client decided to just use ascending sort on the RegionName.

but it was cool to see some input on this.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-20 : 06:28:40

If the client generates the sql, it could generate any order by clause it wants, couldn't it?
Well simple is good

.

>> about 2 weeks
hmm, depends on how much work I get done at work,
that's why Kristen don't let his employees have access to sqlteam

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-20 : 06:44:18

well regions are displayed in one <td></td> tag on the page separated by commas.
sql is generated on the server when the asp page renders.

yeah poor kristen's employees

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-01-20 : 07:58:06

Not so much of the "poor" if you please!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-20 : 08:03:26

He he he,
One day, they too will have the privilege to use sqlteam,
and will have a lot of subemployees, not so privileged !!!

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-20 : 11:11:20

I've got nothing simplier... but i did get it back to the right order (took out the DESC and reversed the @sortorder back to its original state).


declare @sortOrder varchar(500)
set @sortOrder = 'TB,KD,HV'

declare @MyTable table(RegionName Varchar(100))
insert into @MyTable
select 'HV-Region12' union all
select 'HV-Region2' union all
select 'KD-Region5' union all
select 'KD-Region4' union all
select 'KD-Region32' union all
select 'No region' union all
select 'PK-Region blah' union all
select 'RVL-Region ghf' union all
select 'TB-Region geet' union all
select 'USA some Group'

--select * from @MyTable

select *
from @MyTable
order by isnull(nullif(charindex(','+left(RegionName, patindex('%[^a-zA-Z]%', RegionName)-1)+',', ','+@sortOrder+','),0),len(@sortOrder)+1)

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-20 : 12:30:29

you enjoyed playing with this one, didn't you Corey?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-20 : 13:02:05

So much time so little to do....
Great you guys sorted this one out !

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-20 : 13:08:48

Yeah... I did

Too bad that I now have to actually work

Corey

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-20 : 14:33:08

Gee, all along I thougt spirit's code was working !

this will also sort it out, using patindex instead of charindex, otherwise same as corey's:

coalesce(nullif(patindex(left('%'+RegionName,patindex('%[^a-zA-Z]%',RegionName))+'%',@sortOrder),0),len(@sortOrder))

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources