Article: Ordering Rows Using a User-Defined Order

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

Site Related Forums

Article Discussion

Article: Ordering Rows Using a User-Defined Order

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-09-01 : 11:55:25

Jonathan writes: "How would I go about ordering results using a specified order in order to offset and limit the results. Say I have non-unique ids, (23, 12, 98, 3) and I need to return rows that contain these ids in the order I specified above. How could I do this without using any extra queries or sub-queries?

Article Link.

SeanTWarner
Starting Member

1 Post

Posted - 2003-06-11 : 15:56:57

A quick/Simple Solution if your set of keys or options are small Although the sort table will support larger sets and will perform better using indexes, the following solution can be implemented and the sort order can be dynamicly passed into a proc

Examples:
-- Note: depending on data, use the '~' , ',' , Pipe or other charater must be concatenated to the sort field when using the charindex

Set @SortString = '23~12~98~3~'

SELECT ID, yada
FROM foo
ORDER BY CharIndex(Convert(varchar(3),id) + '~', @SortString)

-- or
Set @SortString = 'PA|NJ|AZ|'
SELECT State, yada
FROM foo
ORDER BY CharIndex(State + '|', @SortString)

-- Note: other states will be first (Charindex 0)

SELECT State, yada
FROM foo
ORDER BY CharIndex(State + '|', @SortString + state + '|')
-- Note: other states will be last(Charindex will find it at the end if it is not in the "sort String")

-- Northwind table sample
-- more complex, let's cover everything!

Declare @SortString VarChar(255)

Set @SortString= 'RJ|AK|CA|Lara|Québec|'

SELECT *
FROM Customers
ORDER BY CHARINDEX(ISNULL(RTRIM(Region), 'XXX') + '|',
@SortString + ISNULL(RTRIM(Region), 'XXX') + '|')
-- Add the default sort for any remaining Regions
, ISNULL(RTRIM(Region), 'XXX')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 23:15:41

Nice one, Sean.

For best performance, parse the string and fill up a table variable or a temp table, along with an identity column which ends up being the order by which you sort. similiar to the CSV-to-table techniques that have been discussed here many times.

i.e.,

declare @t (ID varchar(2), sort int identity)

for each token in the string
insert into @t (value)
values (token) -- note that the identity is incrementing
next

then:

select yourdata.*
from
yourdata
inner join
@t
on
yourdata.ID= @t.ID
order by @t.Sort

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-12 : 07:04:58

And there we are, right back where we started.

Jay White
{0}

viraldesai
Starting Member

1 Post

Posted - 2009-11-11 : 23:46:08

i have a table #temp1(id, Name groupname ) and record
like this 1 R1 S
2 R3 S
3 R2 S
4 R4 D
5 R5 D
6 R6 K
7 R7 K
8 R8 L
9 R9 L
10 R10 L
11 R11 K

and i want to display record based on user defind sorting
order e.g.
1 R4 D
2 R5 D
3 R6 K
4 R7 K
5 R11 K
6 R1 S
7 R3 S
8 R2 S
9 R8 L
10 R9 L
11 R10 L

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-11 : 23:49:50

viraldesai,

Have you take a look at the article ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources