List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 27 2010 5:18pm
Subject:Re: How to select rows from only the first N rows in a table?
View as plain text  

> 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 

Thread
How to select rows from only the first N rows in a table?Peng Yu27 Apr
  • Re: How to select rows from only the first N rows in a table?Martijn Tonies27 Apr
    • Re: How to select rows from only the first N rows in a table?Dan Nelson27 Apr
  • Re: How to select rows from only the first N rows in a table?Martijn Tonies27 Apr