How to select N records from a table in mysql

Question

How can I get only 10 records from a table where there are more than 1000 records. I have a test table with rowid, name, cost.

   select  name, cost from test; 

here I want to select only first 10 rows and dont want to select rowid.

Answer 1

To select the first ten records you can use LIMIT followed by the number of records you need:

SELECT name, cost FROM test LIMIT 10 

To select ten records from a specific location, you can use LIMIT 10, 100

SELECT name, cost FROM test LIMIT 100, 10 

This will display records 101-110

SELECT name, cost FROM test LIMIT 10, 100 

This will display records 11-111

To make sure you retrieve the correct results, make sure you ORDER BY the results too, otherwise the returned rows may be random-ish

You can read more @ http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

Answer 2

You should have an ORDER BY clause when you use LIMIT, so that you will get the same recordset if you call it two times in succession and no data has changed.

So, do something like:

select  name, cost  from test  order by rowid limit 10;