| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
SeanTWarner
Starting Member
USA
1 Posts |
 Posted - 06/11/2003 : 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
USA
7423 Posts |
Posted - 06/11/2003 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/12/2003 : 07:04:58
|
And there we are, right back where we started. 
Jay White
{0} |
|
|
|
viraldesai
Starting Member
USA
1 Posts |
Posted - 11/11/2009 : 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)
Singapore
16745 Posts |
Posted - 11/11/2009 : 23:49:50
|
viraldesai,
Have you take a look at the article ?
KH
Time is always against us
|
|
|
| |
Topic |
|
|
|
Article: Ordering Rows Using a User-Defined Order
