Professional Web Applications Themes

How do I get MySQL to NOT sort my resultset? - MySQL

Hi All The following query works, but MySQL sorts the results set: SELECT STRINGTEXT FROM WEBSTRINGS WHERE GUI=0 AND LANGID='GB' AND TOKENID IN (312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62, 63,87,88,89,90,208,210,249,309,310,311); This means that when I grab this in my recordset the data for TOKENID 312 is at the end rather than being the first one, eg I expected my resultset to come back in the following order of requests: 312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,6 3,87,88,89,90,208,210,249,309,310,311 but it comes back as: 47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,63,87 ,88,89,90,208,210,249,309,310,311,312 Is there anyway to get MySQL to not do this for this query? I really need them to come back as is. Thanks Laphan...

  1. #1

    Default How do I get MySQL to NOT sort my resultset?

    Hi All

    The following query works, but MySQL sorts the results set:

    SELECT STRINGTEXT FROM WEBSTRINGS WHERE GUI=0 AND LANGID='GB' AND TOKENID IN
    (312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62, 63,87,88,89,90,208,210,249,309,310,311);

    This means that when I grab this in my recordset the data for TOKENID 312 is
    at the end rather than being the first one, eg

    I expected my resultset to come back in the following order of requests:

    312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,6 3,87,88,89,90,208,210,249,309,310,311

    but it comes back as:

    47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,63,87 ,88,89,90,208,210,249,309,310,311,312

    Is there anyway to get MySQL to not do this for this query? I really need
    them to come back as is.

    Thanks

    Laphan


    Laphan Guest

  2. #2

    Default Re: How do I get MySQL to NOT sort my resultset?

    Laphan wrote:
    > Hi All
    >
    > The following query works, but MySQL sorts the results set:
    >
    > SELECT STRINGTEXT FROM WEBSTRINGS WHERE GUI=0 AND LANGID='GB' AND TOKENID IN
    > (312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62, 63,87,88,89,90,208,210,249,309,310,311);
    >
    > This means that when I grab this in my recordset the data for TOKENID 312 is
    > at the end rather than being the first one, eg
    >
    > I expected my resultset to come back in the following order of requests:
    >
    > 312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,6 3,87,88,89,90,208,210,249,309,310,311
    >
    > but it comes back as:
    >
    > 47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,63,87 ,88,89,90,208,210,249,309,310,311,312
    >
    > Is there anyway to get MySQL to not do this for this query? I really need
    > them to come back as is.
    >
    > Thanks
    >
    > Laphan
    >
    >
    MySQL *DOES NOT SORT* your results unless you explicitly ask for it.
    They are returned in unspecified order, which is the fastest order the DBMS
    finds your rows.
    What you specify in the IN (...) clause is just a list of keys to match, and has nothing
    to do with sorting.

    That said, if you want to sort using a specific list, you can create a support table to achieve
    this result.
    For example:


    desc main_table;
    +----------+----------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | | |
    | contents | char(10) | YES | | | |
    +----------+----------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

    select * from main_table;
    +-----+----------+
    | id | contents |
    +-----+----------+
    | 1 | a |
    | 2 | b |
    | 3 | c |
    | 100 | aa |
    | 200 | bb |
    | 300 | cc |
    +-----+----------+
    6 rows in set (0.00 sec)

    select * from main_table where id in (200,2,100);
    +-----+----------+
    | id | contents |
    +-----+----------+
    | 2 | b |
    | 100 | aa |
    | 200 | bb |
    +-----+----------+
    3 rows in set (0.02 sec)

    Here I asked for records 200, 2, and 100, but, without a ORDER BY clause, the DBMS
    returns them in the prder it finds them.

    Let's use a support table:

    desc sorting_table;
    +------------+---------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+---------+------+-----+---------+----------------+
    | sort_order | int(11) | NO | PRI | | auto_increment |
    | fk_id | int(11) | YES | | | |
    +------------+---------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)

    truncate sorting_table;
    insert into sorting_table (fk_id) values (200), (2), (100);
    # this will insert our records in the order want them.

    select * from sorting_table;
    +------------+-------+
    | sort_order | fk_id |
    +------------+-------+
    | 1 | 200 |
    | 2 | 2 |
    | 3 | 100 |
    +------------+-------+
    3 rows in set (0.00 sec)


    Now we are ready to get the record in our customized order:

    select
    main_table.*
    from
    main_table
    inner join sorting_table on (id=fk_id)
    where
    id in (200,100,2)
    order by
    sort_order;
    +-----+----------+
    | id | contents |
    +-----+----------+
    | 200 | bb |
    | 2 | b |
    | 100 | aa |
    +-----+----------+
    3 rows in set (0.00 sec)

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

Similar Threads

  1. sort my results in php w/ mysql
    By jmack159 in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 28th, 09:14 PM
  2. mysql resultset in flash
    By robin kohli in forum Macromedia Flash
    Replies: 1
    Last Post: November 1st, 02:36 AM
  3. simples way to sort a mysql result
    By somaBoy MX in forum PHP Development
    Replies: 5
    Last Post: November 7th, 05:44 PM
  4. Replies: 1
    Last Post: September 9th, 07:16 AM
  5. Sort Multiple Arrays from MySQL DB
    By James in forum PHP Development
    Replies: 0
    Last Post: August 29th, 02:58 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