Multiple OR's for Listing, Sorting

[deleted]

I have the following MySQL query, used to fetch names from my database from a list of ID's:

SELECT name, id
FROM artists
WHERE id = 424 OR id = 457 OR id = 470 OR id = 6671 OR id = 429 OR id = 6480 OR id = 42 OR id = 5087

This code works (almost) fine with one exception--it prints the list in ascending order by ID! I want the list in the same order (i.e., 424's 'name' is first, then 457's and so on.)

I find this behavior odd because no ORDER BY clause is present.

khaldryck

you can't (at least i never heard of it)

not specifying any order by will return the result in the default order (usually Id asc).

instead of doing multiple OR, i suggest you using the in

like this select * from table where id in (1,2,3,4,5,10,15);

superwormy

Omitting an ORDER BY clause means that the database server will just return results in whatever order if finds them, ie, no logical order at all :-)

Theres really no way to order like that.

You might be better off running a bunch of individual queries in order:

id 424
SELECT name, id FROM artists WHERE ID = 424 LIMIT 1

id 457
SELECT name, id FROM artsists WHERE ID = 457 LIMIT 1

Also, the IN () clause is much nicer than all the OR's, and faster too.

Sxooter

Originally posted by kevinmarsh
I have the following MySQL query, used to fetch names from my database from a list of ID's:
SELECT name, id
FROM artists
WHERE id = 424 OR id = 457 OR id = 470 OR id = 6671 OR id = 429 OR id = 6480 OR id = 42 OR id = 5087
This code works (almost) fine with one exception--it prints the list in ascending order by ID! I want the list in the same order (i.e., 424's 'name' is first, then 457's and so on.)

I find this behavior odd because no ORDER BY clause is present. [/B]

No, this is normal. No order by means no order. And it doesn't mean random, uniform order, it means that the database stopped caring about order, so any internal sorting algorhythm it's using doesn't have to care.

You'll have to force the database to order by using the case statement, or you can order it in an array when you get it in php.

Here's how with a case statement:

select name, id, case id
when 1 then 2
when 2 then 4
when 3 then 7
when 4 then 3
when 5 then 5
when 6 then 8
when 7 then 6
when 8 then 9
when 9 then 10
when 10 then 9
end as ob
from test
order by ob;

superwormy

Oooh... something I did not know... Sxooter is the case statement standard SQL, boiling down to will it work in PostgreSQL and MySQL... and other databases too?

What about speed, is the case statement going to slow me down any if I use it on somethign with like 200 or so records...?

superwormy

Dude kevinmarsh, Sxooter's solution works AWESOME. Way cool sxooter thanks dude, learn something new everyday :-)\

As an added note, you can still use the faster IN () statement now too...

SELECT ID, parent_ID, link, CASE ID
WHEN 4 THEN 1
WHEN 3 THEN 15
WHEN 2 THEN 3
WHEN 1 THEN 4
WHEN 5 THEN 82
WHEN 6 THEN 91
END AS CaseOrder
FROM category
WHERE ID IN ( 2, 3, 6, 4, 1, 5 )
ORDER BY CaseOrder

Sxooter

Yep, case is standard SQL, if a bit unknown / underused. It's one of those things to keep in the big bag of tricks :-)

Current Postgresql 7.3.x is still kinda slow at in(1,2,3) right now for larger numbers of items (say more than a dozen or two) but in CVS tip (hence coming in 7.4) Tom Lane's sped it up quite a bit, so that now using in() with hundreds, even thousands of elements is very fast.

superwormy

Awesome, thanks for the new trick and the heads up about PostgreSQL in the future!