Subquery result sorting

[superhaggis]

Hello there, I'm hoping you can help me out.  I'm trying to get a list of IDs using the IN() subquery syntax.

[code]
mysql> create table books (id int not null auto_increment, title varchar(255) not null, primary key (id));
mysql> insert into books (title) values ('Book A');
mysql> insert into books (title) values ('Book B');
mysql> insert into books (title) values ('Book C');
mysql> insert into books (title) values ('Book D');

mysql> select id from books where id in (2,1,3,4);
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
[/code]
What I am looking for is:
[code]
+----+
| id |
+----+
|  2 |
|  1 |
|  3 |
|  4 |
+----+
[/code]
Anyone know if this is possible, without having to change column types or any other trickery? TIA!

[gluck]

See mysql always returns data in the sequence it was entered. Here the first id entered is 1 so the moment it matches the first id with the IN clause value it will return 1. The sequence of elements in the IN clause doesn't matter. so update the data sequence and you will get your desired results.

[btherl]

The only way to reliably order results in SQL is to use "ORDER BY".  That means you need a column to order by.  You can add an extra column which defines the sorting order, and order by that.

The problem with relying on the order of data entry is that it may change, due to backups/restores, table optimization or other things.

But the appropriate solution for your situation really depends on why you want to order your results that way.

[fenway]

You can actually use ORDER BY FIELD( id, 2,1,3,4 ), if I remember correctly.