CODEWITHSUNDEEP
phpmysqlsqldatabase
Hard worker
Hard worker

Reputation: 4046

How to order mysql results in the order I specify the row id's inside the "IN" condition

The query is:

   SELECT * FROM `stock` WHERE `stockid` IN (33, 12,53,4,99,88)

But when I run the query it doesn't return the rows in the order of 33, 12, 53 etc

I am using php. The above is a simplified example.

Upvotes: 2

Views: 44

Answers (3)

cn0047
cn0047

Reputation: 17071

Don't think that it's really answer, but it another way:

SELECT * FROM stock WHERE stockid IN (33)
UNION SELECT * FROM stock WHERE stockid IN (12)
UNION SELECT * FROM stock WHERE stockid IN (53)
UNION SELECT * FROM stock WHERE stockid IN (4)
UNION SELECT * FROM stock WHERE stockid IN (99)
UNION SELECT * FROM stock WHERE stockid IN (88)
;

Upvotes: 0

Javaluca
Javaluca

Reputation: 857

Maybe you can create a temporary table with two column ('stockIdColumn', 'orderColumn'). 

| STOCKID | ORDER |
|      33 |     1 |
|      12 |     2 |
|      53 |     3 |
|       4 |     4 |
|      99 |     5 |
!      88 |     6 |

Join this table with your and order by column [ORDER]

SELECT s.*
FROM stock s JOIN temp t ON t.stockId = s.id
ORDER BY t.order

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use order by field

SELECT * FROM `stock` WHERE `stockid` IN (33, 12,53,4,99,88)
order by field(stockid,33, 12,53,4,99,88)

Upvotes: 4

Related Questions