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?

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:
> 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,

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" <EMAIL REMOVED> wrote in message
news:EMAIL REMOVED oups.com...
>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?
>

AlterEgo wrote:
> 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
>


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

"drec" <EMAIL REMOVED> wrote in news:1169065722.513991.164730
@l53g2000cwa.googlegroups.com:

>
> AlterEgo wrote:
>>
>> 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
>>
> And if I'm not on 5.X I take it it's time to upgrade?

You could also materialize the subquery in a temporary table first


--
felix