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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Ordering Rows Using a User-Defined Order
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 09/01/2000 :  11:55:25  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 Posts

Posted - 06/11/2003 :  15:56:57  Show Profile  Send SeanTWarner an AOL message  Reply with Quote
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 - 06/11/2003 :  23:15:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Flowing Fount of Yak Knowledge

2878 Posts

Posted - 06/12/2003 :  07:04:58  Show Profile  Reply with Quote
And there we are, right back where we started.

Jay White
Go to Top of Page

Starting Member

1 Posts

Posted - 11/11/2009 :  23:46:08  Show Profile  Reply with Quote
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 - 11/11/2009 :  23:49:50  Show Profile  Reply with Quote

Have you take a look at the article ?

Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000