Getting back set order from the IN param

Hi All

My query is as follows:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)

All I want is my resultset to come back in the order that I have defined in
the IN clause, but unfortunately SQL is trying to be too helpful and sorts
the numbers in the IN clause so that the resultset comes back with a TOKENID
order of 4,6,19,20,32,177,234,800.

I don't want this bloody order I want 6,20,234,19,32,4,800,177!!

Sorry for my rant, but its got my hot under the collar.

Is there anyway round this?

Thanks

Yobbo

Re: Getting back set order from the IN param

"Yobbo" wrote:

> My query is as follows:
>
> SELECT STRINGTEXT, TOKENID
> FROM WEBSTRINGS
> WHERE TOKENID IN (6,20,234,19,32,4,800,177)
>
> All I want is my resultset to come back in the order that I have defined in
> the IN clause,

SQL does not guarantee a certain order of the result set unless you
explicitly ordered one via an ORDER BY clause. If you want a certain
order, you have to specify it via ORDER BY.

> but unfortunately SQL is trying to be too helpful and sorts
> the numbers in the IN clause so that the resultset comes back with a TOKENID
> order of 4,6,19,20,32,177,234,800.

This is a mere coincidence. In fact MySQL sorts the values in the IN
clause in order to be able to do an efficient search on the index.
Therefor you get your result in index order - which is ascending for
most storage engines. If your query hits a MERGE or cluster table the
result order would be data dependent or completely random.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Re: Getting back set order from the IN param

Yobbo wrote:
> SELECT STRINGTEXT, TOKENID
> FROM WEBSTRINGS
> WHERE TOKENID IN (6,20,234,19,32,4,800,177)
>
> All I want is my resultset to come back in the order that I have defined in
> the IN clause

See the docs for FIND_IN_SET() here:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

For example:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)
ORDER BY FIND_IN_SET(TOKENID, '6,20,234,19,32,4,800,177')

Note the quotes: IN() has variable arguments which are a
comma-separated list of integers, but FIND_IN_SET() has two arguments,
the latter of which is a quoted string.

Regards,
Bill K.

Re: Getting back set order from the IN param

Hi Bill

Many thanks for this.

Do you know if your method is more efficient than Dimitre's
FIELD(TOKENID,6,20,234,19,32,4,800,177) method??

Rgds Yobbo



"Bill Karwin" wrote in message
news:eimeo201jf8@enews3.newsguy.com...
Yobbo wrote:
> SELECT STRINGTEXT, TOKENID
> FROM WEBSTRINGS
> WHERE TOKENID IN (6,20,234,19,32,4,800,177)
>
> All I want is my resultset to come back in the order that I have defined
> in
> the IN clause

See the docs for FIND_IN_SET() here:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

For example:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)
ORDER BY FIND_IN_SET(TOKENID, '6,20,234,19,32,4,800,177')

Note the quotes: IN() has variable arguments which are a
comma-separated list of integers, but FIND_IN_SET() has two arguments,
the latter of which is a quoted string.

Regards,
Bill K.

Re: Getting back set order from the IN param

Yobbo wrote:
> Do you know if your method is more efficient than Dimitre's
> FIELD(TOKENID,6,20,234,19,32,4,800,177) method??


I don't know for sure. It may depend partly on your indexes, data
distribution, etc. One way to know for sure is for you to try both
methods under some benchmarking tool (e.g.
http://xaprb.com/mysql-query-profiler/).

Regards,
Bill K.