Archives

The List

« FORMAT() :: MySQL Function of the Day | Main | You’ve Got Six Months (if You’re Lucky!) »

MySQL Pop Quiz #19

By Carsten | March 26, 2008

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

This entry comes from Rudy Limeback

A user searching your website is moving through categories, and you want to display descriptions of these categories as the user progresses. Your basic SELECT looks like this:

SELECT id, description
FROM descriptions
WHERE id IN (23, 6, 9, 37)

You want the descriptions (records) to appear in the same order as the IDs listed in the WHERE clause.

How do you do that?


Show answer

Do you have your own favorite MySQL question? Is there something in MySQL that has you scratching your head? Send your ideas for new pop quiz questions to carsten (at) bitbybit (dot) dk. Send it in, even if you don’t know the answer — that only makes it more interesting.
More Pop Quiz entries: WWW or RSS.

Topics: MySQL, Pop Quiz |

11 Responses to “MySQL Pop Quiz #19”

  1. safari Says:
    March 26th, 2008 at 11:06 am

    be noticed that this kind of ORDER BY will require a filesort.

  2. Alex Says:
    March 26th, 2008 at 11:36 am

    ORDER BY `id` = 23 DESC, `id` = 6 DESC, `id` = 9 DESC, `id` = 37 DESC

    This will also work

  3. Parvesh Garg Says:
    March 27th, 2008 at 11:33 am

    Sorry, but I did published the same some 6 months ago here: http://optimmysql.blogspot.com/2007/09/customized-order-by-sequence-small-hack.html :). Anyways, thanks for the POP Quiz.

  4. fenixshadow Says:
    March 27th, 2008 at 12:18 pm

    order by instr(’,23, 6, 9, 37,’, ‘,’+id+’,')

    will also work

  5. Carsten Says:
    March 27th, 2008 at 8:02 pm

    Thanks all of you for the other solutions.

    Parvesh: I’m not claiming any of this is particularly original, and a lot of this comes by as a result with other people. It’s just another way to enlighten your day a bit.

  6. Parvesh Garg Says:
    March 28th, 2008 at 12:44 pm

    @Carsten
    I know about the POP QUIZ and I’m a great fan of it too. That was just a joke for when you said “I only wish Rudy had told me this 6 months ago when I was trying to do something exactly like that”.

    Anyways, keep up the cool thing going.

  7. Carsten Says:
    March 28th, 2008 at 1:51 pm

    @Parvesh: Ah, right … was a bit slow on the uptake there :-)

  8. Mark Says:
    March 28th, 2008 at 10:32 pm

    Very cool. I actually had been contemplating this just this week - have a table of tennis results - the name of the rounds are not easily understood - 128, r64, r32, r16, qf, sf, f - this technique works great. Thanks.

  9. Carsten Says:
    March 29th, 2008 at 9:25 am

    @Mark: Good to know that these things are being used out there in the wild and not just for entertainment.

  10. Guerrero de Terracota » MySQL Pop Quiz #19 Says:
    March 31st, 2008 at 9:27 am

    [...] ???????????? ? ?????????? Carsten Pedersen. ???????? [...]

  11. Mark Says:
    March 31st, 2008 at 4:33 pm

    Carsten, you’re welcome. Keep the tips coming! I’m sure I’m not the only intermediate MySQLer out there. And in case any others were wondering this works with multiple Order By options; e.g. I have a field for the date of a tournament, eventd, so:

    Select …
    Order By eventd, field(round,’128′,’r64′,…’f')

    works and gives me chronology of both the date and rounds of the tournaments.

Comments