List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 27 2010 3:25pm
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.

-- 
	Dan Nelson
	dnelson@stripped
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