SQL - order by list order

I have the following query that returns rows based on a comma seperated list

Select * from Table where RecordID in (22,15,105,1,65,32)

I would like the results of this query to return to in the order of the ID's in the list. Is 开发者_如何学Cthat possible with SQL?

Thanks in advance

select * from Table
where RecordID in (22,15,105,1,65,32)
order by (
    case RecordID 
        when 22 then 1
        when 15 then 2
        when 105 then 3
        when 1 then 4
        when 65 then 5
        when 32 then 6 end)

If you need the output to appear in a particular order, then you need to specify that order, using something the server can sort. Not knowing which engine you're working against, the general scheme would be to create a temp table or use rowset constructors to pair each record ID with its desired sort order.

E.g. (SQL Server)

declare @T table (RecordID int,Position int)
insert into @T (RecordID,Position)
select 22,1 union all
select 15,2 union all
select 105,3 union all
select 1,4 union all
select 65,5 union all
select 32,6

select * from Table t inner join @T t2 on t.RecordID = t2.RecordID order by t2.Position

I'd to the ordering in the client, but if you really want to do it in SQL, do it like this:

declare @T table (id int identity(1,1), RecordID int)

insert into @T (RecordID)
values (22), (15), (105), (1), (65), (32)

select * from 
[table] t 
inner join @t s on t.id=s.recordid
where t.id in (22, 15, 105, 1, 65, 32)
order by s.id

(works in SQL Server 2008)

Yes. You add the ORDER BY recordedid clause at the end.

The last time I had to do this I ended up doing a union all and generating a select statement for each id, i.e.

select * from Table where RecordID = 22
union all
select * from table where recordid = 15

etc.

It was a pain but it worked.

Use ORDER BY against the RecordID

Select * from Table where RecordID in (22,15,105,1,65,32) ORDER BY RecordID