Please start any new threads on our new site at 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 

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.

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

-- 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~'

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|'

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

Go to Top of Page

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.


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


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

- Jeff
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

In (Som, Ni, Yak)

17689 Posts

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

Have you take a look at the article ?

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

Go to Top of Page

- Advertisement -