SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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.

SeanTWarner
Starting Member

USA
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

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


Go to Top of Page

jsmith8858
Dr. Cross Join

USA
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.

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

Page47
Flowing Fount of Yak Knowledge

USA
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
{0}
Go to Top of Page

viraldesai
Starting Member

USA
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

khtan
In (Som, Ni, Yak)

Singapore
17607 Posts

Posted - 11/11/2009 :  23:49:50  Show Profile  Reply with Quote
viraldesai,

Have you take a look at the article ?


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000