CODEWITHSUNDEEP
sqlsql-server-2008
Ram
Ram

Reputation: 11644

Custom order in SQL

We are querying database to retrieve data in following fashion

select a,b,...f from table1 where id in (6,33,1,78,2)

The result I got from query is in following order 1,2,6,33,78.

I want the result in same order (6,33,1,78,2). Is there any way to retrieve the data in same order.

EDIT *I am using SQL 2008*

Upvotes: 6

Views: 1548

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Using a Table Value Constructor:

SELECT a, b, ... f 
FROM 
        table1 
    JOIN
        ( VALUES 
            (1,  6), 
            (2, 33), 
            (3,  1), 
            (4, 78), 
            (5,  2) 
        ) AS ordering (position, id)
      ON  ordering.id = table1.id
ORDER BY position

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

If you use SQL Server you could use charindex.

select A, B
from Table1
where ID in (6,33,1,78,2)
order by charindex(','+cast(ID as varchar(10))+',', ',6,33,1,78,2,')

Upvotes: 2

juergen d
juergen d

Reputation: 204756

add this order by clause

order by case 
         when id = 6 then 1 
         when id = 33 then 2  
         when id = 1 then 3  
         when id = 78 then 4  
         when id = 2 then 5  
      end

If using MySQL you can do this

ORDER BY FIND_IN_SET(id, '6,33,1,78,2')

Upvotes: 9

Robesz
Robesz

Reputation: 1646

I don't know the background, but usually I achieve this custom order in an additional orderIndex column. This way I can manually manage the order when inserting to the table and add this column to the ORDER BY clause of the default queries

Upvotes: 2

Diego
Diego

Reputation: 36146

ugly solution:

select a,b,...f from table1 where id in 6
UNION
select a,b,...f from table1 where id in 33
and so on..

"better" solution: add another column on your query and do case 6 then 0, case 33 then 1 and so on 

select a,b,...f , case id when 6 then 0 when 33 then 1 <and so on> end
from table1 where ...

and then order by this new column

Upvotes: 1

Related Questions