Ordering results using a specified 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

SQL Server 2000 Forums

Transact-SQL (2000)

Ordering results using a specified order

Author

Topic

chris_t
Starting Member

6 Posts

Posted - 2003-11-28 : 07:15:44

Is it possible to order the results from a query in a specified order, from a list, without using another table or temporary table?

e.g.
SELECT table_ID, table_title
FROM table
WHERE table_ID IN
(4, 6, 1, 3, 7, 8, 2)

I'd like this ordered by the list (4, 6, 1, 3, 7, 8, 2).

I hope this makes sense.

If it is not possible that's fine, I'll use a temporary table - but I'd rather not if I don't have to.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-11-28 : 07:40:01

Use an ORDER BY clause

eg:

SELECT table_ID, table_title
FROM table 
WHERE table_ID IN(4, 6, 1, 3, 7, 8, 2)
ORDER BY TABLE_ID

You can also add DESC or ASC to the order by clause if you want the order to be descending or ascending

eg: ORDER BY TABLE_ID DESC
or ORDER BY TABLE_ID ASC

Hope this helps.

You can't teach an old mouse new clicks.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-11-28 : 07:54:43

order by case table_id
when 4 then 1
when 6 then 2
when 1 then 3
when 3 then 4
when 7 then 5
when 8 then 6
when 2 then 7
else 8 end

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-28 : 08:29:22

Assuming your criteria (4,6,1,3,7,8,2) is variable input.
You will have to define your ordering based on the criteria, which means a table join or a dynamic order by clause.
Here is a technique to avoid Dynamic SQL or temp tables. It use a tally table <numbers>

If you are not familiar with this technique here is a good article on tally tables
http://www.sqlteam.com/item.asp?ItemID=2652


create table numbers (n int)
declare @i int; set @i = 0
while @i <=100
begin
     insert into numbers select @i
     set @i = @i+1
end
GO

create table test (n int,val char(4))

insert into test
select 1,'val1'
union all select 2, 'val2'
union all select 3, 'val3'
union all select 4, 'val4'
union all select 5, 'val5'
union all select 6, 'val6'
union all select 7, 'val7'
union all select 8, 'val8'
union all select 9, 'val9'
GO

declare @criteria varchar(30)
set @criteria = '4,6,1,3,7,8,2'

SELECT a.*
FROM test a
INNER JOIN
(
	SELECT NullIf(SubString(',' + @criteria + ',' , n , CharIndex(',' , ',' + @criteria + ',' , n) - n) , '') n 
	FROM numbers
	WHERE n <= Len(',' + @criteria + ',') AND SubString(',' + @criteria + ',' , n - 1, 1) = ',' 
	AND CharIndex(',' , ',' + @criteria + ',' , n) - n > 0
) b
ON b.n = a.n

drop table test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources