Maybe someone more MySQL-savvy can answer this for me. If I have a query:
I can run the query and get a result with 3 lines, but the results are arbitrarily ordered. Can I specify that I want the results in the order they are specified in the WHERE clause? (not necessarily using all those ORs, just as example)

??

first, i dont know your skill level with SQL, but i imagine you arent actually looking for the "ORDER BY" clause.

ive never used mySQL, and also its been a while since ive used SQL. however, i have read that without an ORDER BY clause, the order of the results is "undefined". that is, one implementation of SQL may returned the rows in the order they were inserted, while another one may return them in a randomized order (unlikely, but possible and for illustration purposes).

if you arent looking for the "ORDER BY" clause, and you want to force some "ORDER", then its obvious that you should be looking for "ORDER BY".

Hi -

SQL is based on "set theory" and, by definition, the "tuples" (what you and I normally think of as "rows" or "records") HAVE NO INTRINSIC "order" with respect to each other.

However, in practice you *do* often wish some kind of order: alphabetic (by last name), numeric (by postal code), etc.

Hence the "order by" clause nadroj spoke of. For example:

You cannot have it ordered according to the orde in the WHERE clause. If you query for key=3 or key=17 or key=1 there is no obvious way to get the records back in the sequence 3,17,1.

There must be at least one field which you can order ascending or descending. You can order by one field and then by another field. ORDER BY lastname, firstname.

If you want to order randomly according to you query you could UNION:
select field from table where key=3 UNION select field from table where key=17 UNION ...

That will give the records in the sequence of the select statements in the UNION.

jlinkels

I am aware of ORDER BY, and that's not what I'm looking for.

I think this answers my question. I guess I will have to re-think the problem.

Maybe I should add that I'm currently doing 3 separate queries, each with one item in the WHERE clause, but the overhead for executing queries makes this slow if there are many more (than 3) items.

Maybe you can describe the problem that you're facing and we can think with you.

Without going into details, my problem goes something like this. Say I have a table with two columns:
where "Key" is a key (duh) and "HugeData" contains large BLOBs of data. I need to select a number of rows (could be 2, could be 1000) from the table, sort them according to some rules (which aren't in the database) and then display them in order. The sorting is recursive and I think shuffling around the HugeData values will create a bottleneck, so I do it in two steps: select the Keys (which are small), sort them, and then query again to select the HugeData in the order of the newly-sorted Keys.

The second query is what I'm asking about here. My current solution is to loop over the sorted keys and do one query each, but this is itself a bottleneck.

jinkels UNION hint looks appropriate; iterate through the ordered list of keys, creating a long series of UNION'd queries to get the HugeDatas out.

One possible way of ordering the results as per the order of query values is like this :

Aha! I think this will work! I will try when I get a few minutes!

It does work, they come out in the right order. I have never used "union" before. Thanks to everyone.

Another way.

Put the data of the WHERE clause into another table - say TABLE_B. and rewrite your query thus -

SELECT `SomeField` FROM `SomeTable` a WHERE a.`key`=TABLE_B.'value1' ORDER BY `SomeField`;

This would remove the long query.

By the way, I didn't understand the need for the backquote ` in the SQL statement.

End