mysql order_by question

Question

Query table articles:

select * from articles where article_id in(98,97,96,99)

returned result ordered like

record 96 ...
record 97 ...
record 98 ...
record 99 ...

but I prefer the result ordered the same as 'in(98,97,96,99)'. Any tricks to make it possible? Thanks.

Answer 1

I suppose this might work:

select * from articles where article_id in(98,97,96) order by article_id desc
union
select * from articles where article_id = 99

I can't see any pattern to your ordering so I can't think of any other way to do this.

Answer 2

Shawn, When you use IN Statement, in MYSQL

It compares the First record ID to your given ID. Then compares Second record ID to your given ID. So ...

So the result will be displayed in asc order.

If you need dont use IN statement. I would prefer to Retrieve Each record in loop.

Thankyou

Answer 3

You might be able to do something like this:

select * from articles where article_id in(98,97,96,99) order by article_id == 98 desc, article_id == 97 desc, article_id == 96 desc, article_id == 99 desc;

Answer 4

You'll have to do the sorting through code as it appears that the string 98,97,96,99 is also generated via code. Anyway, see if this helps:

SELECT * FROM
(
SELECT 1 AS SortKey, articles.* FROM articles WHERE article_id = 98
UNION
SELECT 2 AS SortKey, articles.* FROM articles WHERE article_id = 97
UNION
SELECT 3 AS SortKey, articles.* FROM articles WHERE article_id = 96
UNION
SELECT 4 AS SortKey, articles.* FROM articles WHERE article_id = 99
.
.
.
) AS ASDF
ORDER BY SortKey

Answer 5

The IN function is subject to a limit on the size of the list. More likely, once the list gets to a few hundred entries, your performance will drop off - you may as well retrieve the records individually and sort them in the client.

Having said that, there's another MySQL function, FIND_IN_SET you might consider to implement your ORDER BY. FIND_IN_SET can only handle up to 64 members in the set though.

ORDER BY
    FIND_IN_SET( article_id, '98,97,96,99' )