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?
--
Regards,
Peng
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
> 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
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
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@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org