> In the last episode (Apr 27), Martijn Tonies said:
>> > It seems that there is no direct support to limit 'select' to only the
>> > first N rows in a table. Could you let me know what the best way
>> > select
>> > rows from the first N rows in a table is?
>>
>> LIMIT usually works fine ;-)
>>
>> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> That may noy be what Peng is looking for, though. LIMIT filters the
> output
> resultset, not the input table. Since in the logical SQL world, tables
> are
> unsorted collections of rows, it doesn't make sense to limit on them
> directly. You can do this, however:
>
> SELECT * FROM
> (
> SELECT * FROM products ORDER BY DATE LIMIT 10
> ) AS t
> WHERE color='red'
>
> This will fetch the 10 oldest products in the table and then return only
> the
> red ones. Compare to
>
> SELECT * FROM products WHERE color='red' ORDER BY DATE LIMIT 10
>
> which will return the 10 oldest red products, even if they are the 10
> newest
> records in the table.
I fully agree on the differences, and yes, you're very right about unsorted
collections
of rows.
Basically, you're selecting from an intermediate limited resultset set here
via a
derived table.
Not sure what the original posted wanted though.
With regards,
Martijn Tonies
Upscene Productions
http://www.upscene.com
Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!
Database questions? Check the forum:
http://www.databasedevelopmentforum.com
