CODEWITHSUNDEEP
sqldatabasesql-server-2005
Batakj
Batakj

Reputation: 12743

How to get Result from database by order the condition put in "IN" clause?

Sorry for bad english.

bfproduct is a table and productid is primary key in this table and productname is another field defined in this table.

When i execute this query, select * from bfproduct where productid in (23,5,54,3132,32). The result is as follows:

 

productid | productname

5         15 Park Avenue
23        Good Boy Bad Boy
32        dsf sf gfdsf dsf d  
54       dsdsfsa ffs ff sfsf 
3132        Just Books - On The Failure of Legal System

Is there any way i will get the resultset in the order by the productid provided in "IN" Clause e.g.


productid | productname
23        Good Boy Bad Boy
5         15 Park Avenue
54       dsdsfsa ffs ff sfsf 
3132        Just Books - On The Failure of Legal System
32        dsf sf gfdsf dsf d

Please help...

Upvotes: 2

Views: 187

Answers (3)

barrylloyd
barrylloyd

Reputation: 1589

First thing I can think of, try something like...

select  bfproduct.*
from    bfproduct INNER JOIN
(
    select 1 as sequence, 23 as productid
    union
    select 2,5
    union
    select 3,54
    union
    select 4,3132
    union
    select 5,32
) as lookup on bfproduct.productid=lookup.productid
order by lookup.sequence

(I haven't tested this so there maybe some minor syntax errors!)

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838896

Here's one way to do it:

SELECT *
FROM bfproduct
WHERE productid
IN (23,5,54,3132,32)
ORDER BY
   CASE productid
      WHEN   23 THEN 0
      WHEN    5 THEN 1
      WHEN   54 THEN 2
      WHEN 3132 THEN 3
      WHEN   32 THEN 4
   END

Upvotes: 9

Juha Syrjälä
Juha Syrjälä

Reputation: 34281

You have to add a ORDER BY clause that puts the rows to correct order.

Upvotes: 0

Related Questions