CODEWITHSUNDEEP
mysql
gpwr
gpwr

Reputation: 1025

mysql default sort order with specific selects

I get a lot of answers when it comes to how MySQL sorts results but none seems to address my specific issue. Hope I didn't miss the answer somewhere else here on stackoverflow.

I have an SQL query that looks like this:

SELECT id, something FROM sometable WHERE 
id=1 OR id=2 OR id=5 OR 
id=6 OR id=100 OR id=1000 
OR id=4

Now I need it to return the results in the specific order it has been selected. The results should look like this in this specific order:

1
2
5
6
100
1000
4

Will MYSQL display the returned records in this particular order the way I selected them? I cannot use ORDER BY because I need them the exact way I selected them in the first instance. A simple test confirms that it is returned in this way but from reading elsewhere I get the idea that you cannot trust the way results are being returned in specific order when ORDER BY is not used.

Upvotes: 0

Views: 41

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use FIELD() function. In your application code, where you will be creating this SQL dynamically, you can build the SQL string in the same order for FIELD(), as in your WHERE clause.

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

Now, the FIELD(id, 1,2,5,6,100,1000,4) function will return 0 if the id is not in (1,2,5,6,100,1000,4).

So, if we use ORDER BY FIELD(id, 1,2,5,6,100,1000,4) only, other non-matching rows will appear at the top. So, we can use If() function to return 1 for the other non-matching rows, and 0 for the matched rows.

Now, we can utilize one more level of ordering by FIELD(id, 1,2,5,6,100,1000,4). This will ensure that the matched id(s) appear first in the order as specified in the Field() function.

SELECT id, something FROM sometable WHERE 
id=1 AND id=2 AND  id=5 AND  
id=6 AND  id=100 AND id=1000 
AND id=4 
ORDER BY IF(FIELD(id, 1,2,5,6,100,1000,4) = 0, 1, 0) ASC, 
         FIELD(id, 1,2,5,6,100,1000,4) ASC

Upvotes: 2

Related Questions