Professional Web Applications Themes

Getting back set order from the IN param - MySQL

Hi All My query is as follows: SELECT STRINGTEXT, TOKENID FROM WEBSTRINGS WHERE TOKENID IN (6,20,234,19,32,4,800,177) All I want is my resultset to come back in the order that I have defined in the IN clause, but unfortunately SQL is trying to be too helpful and sorts the numbers in the IN clause so that the resultset comes back with a TOKENID order of 4,6,19,20,32,177,234,800. I don't want this bloody order I want 6,20,234,19,32,4,800,177!! Sorry for my rant, but its got my hot under the collar. Is there anyway round this? Thanks Yobbo...

  1. #1

    Default Getting back set order from the IN param

    Hi All

    My query is as follows:

    SELECT STRINGTEXT, TOKENID
    FROM WEBSTRINGS
    WHERE TOKENID IN (6,20,234,19,32,4,800,177)

    All I want is my resultset to come back in the order that I have defined in
    the IN clause, but unfortunately SQL is trying to be too helpful and sorts
    the numbers in the IN clause so that the resultset comes back with a TOKENID
    order of 4,6,19,20,32,177,234,800.

    I don't want this bloody order I want 6,20,234,19,32,4,800,177!!

    Sorry for my rant, but its got my hot under the collar.

    Is there anyway round this?

    Thanks

    Yobbo




    Yobbo Guest

  2. #2

    Default Re: Getting back set order from the IN param

     

    Far from elegant:

    SELECT STRINGTEXT, TOKENID
    FROM WEBSTRINGS
    WHERE TOKENID IN (6,20,234,19,32,4,800,177)
    ORDER BY
    TOKENID=6 DESC,
    TOKENID=20 DESC,
    TOKENID=234 DESC
    TOKENID=19 DESC,
    TOKENID=32 DESC,
    TOKENID=4 DESC,
    TOKENID=800 DESC,
    TOKENID=177 DESC;


    Regards
    Dimitre


    Radoulov, Guest

  3. #3

    Default Re: Getting back set order from the IN param

     
    >
    > Far from elegant:
    >
    > SELECT STRINGTEXT, TOKENID
    > FROM WEBSTRINGS
    > WHERE TOKENID IN (6,20,234,19,32,4,800,177)
    > ORDER BY
    > TOKENID=6 DESC,
    > TOKENID=20 DESC,
    > TOKENID=234 DESC[/ref]
    ....
    [...]

    This one is better :)

    SELECT STRINGTEXT, TOKENID
    FROM WEBSTRINGS
    WHERE TOKENID IN (6,20,234,19,32,4,800,177)
    ORDER BY
    FIELD(TOKENID,6,20,234,19,32,4,800,177);


    Regards
    Dimitre





    Radoulov, Guest

  4. #4

    Default Re: Getting back set order from the IN param

    "Yobbo" <co.uk> wrote:
     

    SQL does not guarantee a certain order of the result set unless you
    explicitly ordered one via an ORDER BY clause. If you want a certain
    order, you have to specify it via ORDER BY.
     

    This is a mere coincidence. In fact MySQL sorts the values in the IN
    clause in order to be able to do an efficient search on the index.
    Therefor you get your result in index order - which is ascending for
    most storage engines. If your query hits a MERGE or cluster table the
    result order would be data dependent or completely random.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  5. #5

    Default Re: Getting back set order from the IN param

    Many thanks Dimitre

    This looks like a winner will let you know how I get on.

    Rgds Yobbo

    "Radoulov, Dimitre" <com> wrote in message
    news:454dcfd6$0$49200$sunsite.dk...
     
    >
    > Far from elegant:
    >
    > SELECT STRINGTEXT, TOKENID
    > FROM WEBSTRINGS
    > WHERE TOKENID IN (6,20,234,19,32,4,800,177)
    > ORDER BY
    > TOKENID=6 DESC,
    > TOKENID=20 DESC,
    > TOKENID=234 DESC[/ref]
    ....
    [...]

    This one is better :)

    SELECT STRINGTEXT, TOKENID
    FROM WEBSTRINGS
    WHERE TOKENID IN (6,20,234,19,32,4,800,177)
    ORDER BY
    FIELD(TOKENID,6,20,234,19,32,4,800,177);


    Regards
    Dimitre






    Yobbo Guest

  6. #6

    Default Re: Getting back set order from the IN param

    Yobbo wrote: 

    See the docs for FIND_IN_SET() here:
    http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

    For example:

    SELECT STRINGTEXT, TOKENID
    FROM WEBSTRINGS
    WHERE TOKENID IN (6,20,234,19,32,4,800,177)
    ORDER BY FIND_IN_SET(TOKENID, '6,20,234,19,32,4,800,177')

    Note the quotes: IN() has variable arguments which are a
    comma-separated list of integers, but FIND_IN_SET() has two arguments,
    the latter of which is a quoted string.

    Regards,
    Bill K.
    Bill Guest

  7. #7

    Default Re: Getting back set order from the IN param

    Hi Bill

    Many thanks for this.

    Do you know if your method is more efficient than Dimitre's
    FIELD(TOKENID,6,20,234,19,32,4,800,177) method??

    Rgds Yobbo



    "Bill Karwin" <com> wrote in message
    news:newsguy.com...
    Yobbo wrote: 

    See the docs for FIND_IN_SET() here:
    http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

    For example:

    SELECT STRINGTEXT, TOKENID
    FROM WEBSTRINGS
    WHERE TOKENID IN (6,20,234,19,32,4,800,177)
    ORDER BY FIND_IN_SET(TOKENID, '6,20,234,19,32,4,800,177')

    Note the quotes: IN() has variable arguments which are a
    comma-separated list of integers, but FIND_IN_SET() has two arguments,
    the latter of which is a quoted string.

    Regards,
    Bill K.


    Yobbo Guest

  8. #8

    Default Re: Getting back set order from the IN param

    Yobbo wrote: 


    I don't know for sure. It may depend partly on your indexes, data
    distribution, etc. One way to know for sure is for you to try both
    methods under some benchmarking tool (e.g.
    http://xaprb.com/mysql-query-profiler/).

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. JSP tag <mm:param>
    By liurz in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: August 25th, 07:57 AM
  2. cf param
    By DAneHa in forum Macromedia ColdFusion
    Replies: 3
    Last Post: February 25th, 08:45 PM
  3. CGI::Session param help
    By perl@swanmail.com in forum PERL Beginners
    Replies: 2
    Last Post: October 25th, 02:33 AM
  4. sub param in CGI.pm
    By Keith Keller in forum PERL Miscellaneous
    Replies: 2
    Last Post: August 20th, 03:50 AM
  5. url and param in php
    By Cooper in forum PHP Development
    Replies: 0
    Last Post: July 17th, 12:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139