From: Martijn Tonies
Date: April 27 2010 5:18pm
Subject: Re: How to select rows from only the first N rows in a table?
List-Archive: http://lists.mysql.com/mysql/221421
Message-Id: <02a201cae62d$a361e8a0$6101a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit



> 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