my query is:
select * from table where id IN (1,5,4,3,2)
what I want is exactly same as this order not from 1...5 but 1,5,4,3,2. How can I do that?
Asked
Active
Viewed 94 times
0
-
what you mean for custom order ? any specific – Arunkumar Muthuvel Mar 19 '18 at 14:59
-
Possible duplicate of [How to define a custom ORDER BY order in mySQL](https://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql) – Abdul Rauf Mar 19 '18 at 15:01
-
Exact duplication of https://stackoverflow.com/questions/958627/mysql-order-by-values-within-in – Arunkumar Muthuvel Mar 19 '18 at 15:05
1 Answers
1
MYSQL's FIELD function and ORDER BY can help out here.
SELECT
*
FROM
[table]
WHERE
id IN (1,5,4,3,2)
ORDER BY
FIELD(id, 1,5,4,3,2)
negatieve FIELD(id, 1,5,4,3,2) can't make use of the index on id column.
Or without the function FIELD
SELECT
*
FROM (
SELECT
1 AS number
UNION
SELECT
5 AS number
UNION
SELECT
4 AS number
UNION
SELECT
3 AS number
UNION
SELECT
2 AS number
)
AS numbers
INNER JOIN
[table]
ON
[table].id = numbers.number
positive can use a index on id column.
negative harder to understand because of the delivered table
Raymond Nijland
- 11,488
- 2
- 22
- 34
-
only for this query :(, it may some more than 100 rows means what we do? – Arunkumar Muthuvel Mar 19 '18 at 15:03