Professional Web Applications Themes

Mysql Subquery question - MySQL

I am trying to execute a query that will sort 100 records by two columns in consecutive order. For example, lets say I have one table with two fields named "ID" and "DESCRIPTION" . I am currently paging the results by executing the query "SELECT * FROM TABLE ORDER BY ID LIMIT 0,100;" I now want to be able to sort these first 100 records by description but only these 100 records. My problem now is that if I try to sort by description I will not get the first 100 records in the table, instead getting records only sorted ...

  1. #1

    Default Mysql Subquery question

    I am trying to execute a query that will sort 100 records by two
    columns in consecutive order. For example, lets say I have one table
    with two fields named "ID" and "DESCRIPTION" . I am currently paging
    the results by executing the query "SELECT * FROM TABLE ORDER BY ID
    LIMIT 0,100;"

    I now want to be able to sort these first 100 records by description
    but only these 100 records. My problem now is that if I try to sort by
    description I will not get the first 100 records in the table, instead
    getting records only sorted by description. I believe I have to use
    some sort of subquery to do this, but have not been successful in
    trying to do so.

    Any ideas?

    drec Guest

  2. #2

    Default Re: Mysql Subquery question

    drec,

    This should work if you are on 5.x:

    select * from
    (select * from mytable order by id limit 0,100) t
    order by description

    -- Bill

    "drec" <com> wrote in message
    news:googlegroups.com... 


    AlterEgo Guest

  3. #3

    Default Re: Mysql Subquery question

    Hi Drec,

    I may be over simpling your issue, as I'm not totally clear on what you
    are looking for. However, ORDER BY will take a list of values separated
    by ",". So to sort by id then description use:

    "SELECT * FROM TABLE ORDER BY ID, DESCRIPTION LIMIT 0,100;"

    You can also order by Acending or Descenging.

    "SELECT * FROM TABLE ORDER BY ID asc, DESCRIPTION desc LIMIT 0,100;"


    drec wrote: 

    blaine@worldweb.com Guest

  4. #4

    Default Re: Mysql Subquery question


    AlterEgo wrote: 


    And if I'm not on 5.X I take it it's time to upgrade? Thanks for the
    suggestions.

    drec Guest

  5. #5

    Default Re: Mysql Subquery question

    "drec" <com> wrote in news:1169065722.513991.164730
    l53g2000cwa.googlegroups.com:
     
    > And if I'm not on 5.X I take it it's time to upgrade?[/ref]

    You could also materialize the subquery in a temporary table first


    --
    felix
    Felix Guest

Similar Threads

  1. MySQL - WHERE EXISTS Subquery
    By drmaves in forum Coldfusion Database Access
    Replies: 4
    Last Post: December 13th, 02:32 AM
  2. Replies: 0
    Last Post: July 23rd, 12:15 AM
  3. MySQL subquery in select
    By Kasper K in forum MySQL
    Replies: 6
    Last Post: July 12th, 02:24 PM
  4. mySQL subquery problem
    By rmorgan in forum Coldfusion Database Access
    Replies: 7
    Last Post: October 30th, 07:06 AM
  5. SQL subquery question
    By derek in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 06:48 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