Keep order with MySQL “IN” query

     

Consider you have a MySQL table with user id, name, email and city. Let’s say it’s a table containing all of your company’s customers, exactly a hundred clients. Here’s how it looks on phpMyAdmin:

phpMyAdmin view of a dummy SQL table

Well, now your boss has come to you and given you a list of 20 user ids. He wants you to get corresponding names and emails from the database, while keeping the same order of results. The IDs are:

47422, 94621, 83631, 74122, 55269, 78576, 80231, 29246, 13872, 41163, 57480, 77727, 10331, 50420, 20178, 49048, 42938, 12276, 63598, 33599

Now, how do you go about this? Check each name and email manually in phpMyAdmin against this list? It works, but it’s also not so handy method if the list is very long. There’s an automatic way to do it with SQL and phpMyAdmin. The method is essentially called “keeping order with MySQL IN query”. Let me show you how to do it.

Step 1 – Login to your phpMyAdmin and select the correct database from left.

Step 2 – Go to the SQL tab. Here you can run raw SQL queries to the database:

SQL tab of phpMyAdmin

We want to run the following SQL command to retrieve name and email from the table, without messing up with the original order seen in the ID list you were given:

SELECT user_id, name, email
FROM customers
WHERE user_id IN (47422, 94621, 83631, 74122, 55269, 78576, 80231, 29246, 13872, 41163, 57480, 77727, 10331, 50420, 20178, 49048, 42938, 12276, 63598, 33599)
ORDER BY FIELD(user_id, 47422, 94621, 83631, 74122, 55269, 78576, 80231, 29246, 13872, 41163, 57480, 77727, 10331, 50420, 20178, 49048, 42938, 12276, 63598, 33599)

Put the above query to the textbox in SQL tab and hit Go.

You’ll be represented with user ids accompanied with names and emails, in a perfect order:

Using IN and ORDER BY clauses in SQL, as seen in phpMyAdmin

Step 3 – Click the Export link at the bottom of the page. You’ll be shown an export window. Select “Quick” as the Export method and “CSV for MS Excel” as Format.

The export view of phpMyAdmin

Step 4 – Just hit “Go” and you’re given a .CSV file with the data you just fetched. Open it in Excel and you’re good to go (if it looks strange, try with the “CSV” option above the “CSV for MS Excel”):

phpMyAdmin-exported CSV file opened in Excel

You now have an Excel file containing all the user ids, names and emails your boss asked you to get. Not a hard task, was it?

Explanations

Understandably the SQL query requires an explanation. After all, this might not mean much to you:

SELECT user_id, name, email
FROM customers
WHERE user_id IN (47422, 94621, 83631, 74122, 55269, 78576, 80231, 29246, 13872, 41163, 57480, 77727, 10331, 50420, 20178, 49048, 42938, 12276, 63598, 33599)
ORDER BY FIELD(user_id, 47422, 94621, 83631, 74122, 55269, 78576, 80231, 29246, 13872, 41163, 57480, 77727, 10331, 50420, 20178, 49048, 42938, 12276, 63598, 33599)

So here we go:

SELECT: We select user_id, name and email fields. Replace these with field names you want to fetch.

FROM: The table name we want to fetch the data from.

WHERE: A filter for the select. We don’t want to select all one hundred rows, so we restrict the selection with IN clause to certain user ids.

ORDER BY FIELD: This is where the magic happens. ORDER BY makes sure the data is returned in a certain, user-given order. The FIELD(user_id, 47422, 94621…) means we’re using the field user_id with a given order. Remember to give same comma separated list here as you did on the WHERE clause. Otherwise, it wont work! Also note there is no space between the FIELD and the following bracket.

So, now you know how to keep order in MySQL “IN” query. Impress your boss, co-workers or anybody who’s interested in databases!

Share Your Thoughts

Have something to say about this post? Then say it by filling the form below!

(required):

: