MySQL Question

I realize this probably isn’t the place I will ever get the answer I am looking for, but just in case…

I have a MySQL table with a primary key index on id. I would like to, for example, retrieve items with IDs 1, 7, and 4 in that order. I have tried

SELECT * FROM my_table WHERE id IN (1, 7, 4)

and

SELECT * FROM my_table WHERE id = 1 OR id = 7 OR id = 4

without success. Both queries return my results in numerical order. Does anyone know how to set the order from the query. I know how to put the items in order using this query and then some Ruby or PHP, but I want to get them in the order straight from the database and I don’t want to add a sort_order column or anything. Thanks.

2005
10/31
POSTED BY
Travis
CATEGORY
Programming
Write comment
    • Libby
    • November 1st, 2005

    Can’t help you.

    • Wade
    • November 2nd, 2005

    Hope this helps. Pick either the German or Chinese answer.

    Ich habe einen MySQL Tisch mit einem primären Schlüsselindex auf Identifizierung. Ich würde wie zu, zum Beispiel, wiedergewinnt Punkte mit IDENTIFIZIERUNGEN 1, 7, und 4 in das anordnet.

    我在身份证件以一个最初主要的标志有一张 MySQL 桌子。我想要，例如，跟身份证一起取回条款 1，在那种次序中的 7， 4。

    • travis
    • November 2nd, 2005

    thanks Wade, I think I got it now…

    • Alex Leigh
    • November 11th, 2005

    select * from my_table where id = 1 or id = 5 or id = 10 order by id asc

    • travis
    • November 11th, 2005

    okay, but how could I get the from the table in an order, say, 1, 10, 5?

    • Alex Leigh
    • November 16th, 2005

    That’s a very unusual thing to want to do. It supposes that the ID representing the row is somehow meaningful to you other than for purposes of identifying the row, which is extraordinarily bad form – coding business meaning into the values of row ids.

    In 11 years of working with SQL in production environments, I can honestly say I’ve never wanted to do what you are requesting. With that in mind, there is probably a better approach to your problem – in other words, this is not the solution you are looking for.

    That being said, it is possible. If you are applying static transformations to dictate order, eg in this case 1=1, 10=2, 5=3 then you can create a translation table, join the id, and then order by the transform id asc. Then hide it with a view, and never, ever tell anyone what you have done.

    If you have some sort of strange function for dynamically determining order that is not based on the natural order of any of the columns or combination of columns, you could also write a stored procedure to do it, although philsophically this isn’t much different from having the application do it.

    • travis
    • November 16th, 2005

    It’s not bad form at all, I just know the order that I want the elements from the database in. If it were possible to dictate the specific order on an indexed column, it would be a far less expensive operation than the join operation that you are suggesting.

    I can think of quite a few instances (including the one I am going to implement it for) where knowing the order of just a select few ids from the table would be valid and not applying any sort of extra meaning to the ids, speficially in multiuser applications where there could be multiple lists from the same table. Your join table would be at least four columns and all indexed. Clearly more expensive than just getting the three rows you want in the order you want (if it were possible in MySQL).

  1. No trackbacks yet.