Developer Forum

Hi All

The following query works, but MySQL sorts the results set:

SELECT STRINGTEXT FROM WEBSTRINGS WHERE GUI=0 AND LANGID='GB' AND TOKENID IN
(312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,63,87,88,89,90,208,210,249,309,310,311);

This means that when I grab this in my recordset the data for TOKENID 312 is
at the end rather than being the first one, eg

I expected my resultset to come back in the following order of requests:

312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,63,87,88,89,90,208,210,249,309,310,311

but it comes back as:

47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,63,87,88,89,90,208,210,249,309,310,311,312

Is there anyway to get MySQL to not do this for this query? I really need
them to come back as is.

Thanks

Laphan

aphan wrote:

MySQL *DOES NOT SORT* your results unless you explicitly ask for it.
They are returned in unspecified order, which is the fastest order the DBMS
finds your rows.
What you specify in the IN (...) clause is just a list of keys to match, and has nothing
to do with sorting.

That said, if you want to sort using a specific list, you can create a support table to achieve
this result.
For example:


desc main_table;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | | |
| contents | char(10) | YES | | | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

select * from main_table;
+-----+----------+
| id | contents |
+-----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
| 100 | aa |
| 200 | bb |
| 300 | cc |
+-----+----------+
6 rows in set (0.00 sec)

select * from main_table where id in (200,2,100);
+-----+----------+
| id | contents |
+-----+----------+
| 2 | b |
| 100 | aa |
| 200 | bb |
+-----+----------+
3 rows in set (0.02 sec)

Here I asked for records 200, 2, and 100, but, without a ORDER BY clause, the DBMS
returns them in the prder it finds them.

Let's use a support table:

desc sorting_table;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| sort_order | int(11) | NO | PRI | | auto_increment |
| fk_id | int(11) | YES | | | |
+------------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

truncate sorting_table;
insert into sorting_table (fk_id) values (200), (2), (100);
# this will insert our records in the order want them.

select * from sorting_table;
+------------+-------+
| sort_order | fk_id |
+------------+-------+
| 1 | 200 |
| 2 | 2 |
| 3 | 100 |
+------------+-------+
3 rows in set (0.00 sec)


Now we are ready to get the record in our customized order:

select
main_table.*
from
main_table
inner join sorting_table on (id=fk_id)
where
id in (200,100,2)
order by
sort_order;
+-----+----------+
| id | contents |
+-----+----------+
| 200 | bb |
| 2 | b |
| 100 | aa |
+-----+----------+
3 rows in set (0.00 sec)

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.blogspot.com/