SQL: SELECT THE FIRST N RECORDS

Assuming that we want to select the first 8 registrations from the following sample User table

Id	First_Name	Last_Name	City	Registration_Date
1	John	Smith	New York	2009-01-01
2	Adam	King	London	2008-12-01
3	Lin	Lin	Beijing	2009-12-01

Let's start with a MySQL query, which will use the LIMIT clause:

     Select * from user order by registration_date limit 8;

The above query will give the first 8 users by registration date.

Now, if we want to have the first 8 users from New York, you can change the query to this:

     Select * from user WHERE city=’New York’ ORDER BY registration_date limit 8;

Now let’s try the following query:

    SELECT * FROM
       ( SELECT * FROM user ORDER BY registration_date LIMIT 8 ) As  first_8
    WHERE city='New York';

This above query result actually returns the users from New York from the first 8 registrations. The number of records return will be between 0 and 8 in reality because the WHERE clause filter the records from the sub query result set.

Since each database venders use different non-standard result limit syntax, the above query needs to rewrite to accommodate the particular syntax . For example, in Oracle, the query can be rewritten in follow form:

   Select * from user order by registration_date where rownum >= 8;

And in SQL Server, it can be written like this:

   Select TOP 8 * from user order by registration_date;

 Reference:

•  MySQMySQL Cookboook: Chapter 3, Selecting Data FromTables   
•  MySQL Cookbook: Chapter 13,Statistical Techniques.
•  ISO 2008-SQL
• Other Reference On the Web