CODEWITHSUNDEEP
phpmysql
Anish Joseph
Anish Joseph

Reputation: 1026

" where in " mysql clause

i used a mysql query where there is "where id in ("22,20,21") " but when i get the result its like array ordered in asecending order ie 20,21,20

but i need it in the same order as i gave the clause

Upvotes: 6

Views: 23672

Answers (5)

Michael Kopinsky
Michael Kopinsky

Reputation: 904

In general, the database system returns the results in whatever order it feels like. If you want it to be ordered, you have to tell it. This blog post provides one method for doing so; you may be able to find other solutions online.

Upvotes: 0

Crozin
Crozin

Reputation: 44396

As mentioned... WHERE clause doesn't do anything with ordering of result set. Use MySQL built-in FIELD function in ORDER BY clause to specify how the result set should be ordered:

... WHERE id IN (22, 20, 21) ORDER BY FIELD(id, 22, 20, 21) ...

Upvotes: 2

nathan gonzalez
nathan gonzalez

Reputation: 12017

i think you should be able to use the FIELD keyword like so:

SELECT * FROM table
WHERE id in (22,20,21)
ORDER BY FIELD(id,22,20,21);

this is mysql specific, and seems magical, but it works.

Upvotes: 18

Asaph
Asaph

Reputation: 162851

You cannot specify the order results will be returned in in a WHERE clause. If you want to specify order, you need to add an ORDER BY clause to your query. For a solution that uses FIND_IN_SET see this answer.

Upvotes: 0

Adam Vandenberg
Adam Vandenberg

Reputation: 20671

SQL results are unordered unless given an explicit ordering; the order won't be picked up from the in clause.

Since the IDs are neither ASC nor DESC either, you'd have to ORDER BY something else; how are you determining the 22,20,21 order in the first place?

Upvotes: 2

Related Questions