SQL SELECT... IN result order

Question

Here is the scenario - I have a table ``` CREATE TABLE IF NOT EXISTS `stylemaps` ( `zyid` varchar(9) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `styid` varchar(9) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'zzzz0000Z', UNIQUE KEY `zyid` (`zyid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ``` A typical set of entries in this table might be ``` zyid styid qrst1234 abcd1230 abcd9876 abcd1231 pqzx4569 abcd1232 bcde0000 abcd1233 ``` i.e. the order of entries for zyid is fairly random. Now suppose I issue ``` SELECT styid FROM `styles` WHERE zyid in ['abcd9876','bcde0000'] ``` the result I get is ``` abcd1231 abcd1233 ``` i.e. the rows are ordered in the same way as the IN clause my SQL statement. My question is this - can I rely on this always being the case (so long as I order the IN clause entries correctly)? If this ever fails and I end up mapping styles incorrectly the end results are liable to be completely garbled. The alternative would be to do ``` SELECT zyid,styid ``…

Accepted Answer

You can try with:

```
  SELECT styid
    FROM `styles` 
   WHERE zyid IN ['abcd9876','bcde0000']
ORDER BY FIELD(zyid, 'abcd9876','bcde0000')
```

SQL SELECT... IN result order

Solution

Problem