Query to order the result matched a given WHERE IN (a, b, c) condition
I am doing some work to populate a document using a MySQL database. I want to make a result with a given WHERE clause. After the student table:
student
+-----+------------+-----------------+-----+
| id | nickname | student_name | ... |
+-----+------------+-----------------+-----+
| 1 | Joy | Anderson | ... |
| 2 | Prank | Campbell | ... |
+-----+------------+-----------------+-----+
I gave the following query to the database:
SELECT nickname FROM students WHERE student_name in ('Anderson', 'Campbell')
then I expected a result like this:
Joy
Prank
Above the expected result, the sequence in the WHERE clause is matched. (WHERE student_name in ("Anderson", "Campbell")) Joy is mapped to Anderson and Prank is mapped to Campbell. But the current result is this:
Prank
Joy
Now I don't know what I should do to achieve the expected result. Can anyone give me some idea or information for this situation?
source to share
You have fallen into a common SQL trap. Strings like your strings in students
and set members like ('Anderson', 'Campbell')
don't have inline ordering. The server doesn't know anything about Anderson
which precedes Campbell
, although your request shows them this way.
Your only resource is to use the relevant offer ORDER BY
. Without a sentence, the ORDER BY
results are displayed in an order that is formally unpredictable. In your case, the ORDER BY student_name
order of the rows will be ordered at the end of your query.
Unpredictable is a tricky idea. It's like casual but worse. Randomness usually means that the outcome is likely to be different every time. Unpredictable means that they are the same, while they are not.
source to share