eXtremeSQL Limit

The LIMIT expression allows one to limit the number of result set rows in a query. For example:

 
    SELECT contact_id, last_name, first_name
    FROM contacts
    WHERE website = 'TechOnTheNet.com'
    ORDER BY contact_id DESC
    LIMIT 5;
     

will select the first 5 records from the contacts table where the website is TechOnTheNet.com. Note that the results are sorted by contact_id in descending order so this means that the 5 largest contact_id values will be returned. If there are other records in the contacts table that have a website value of TechOnTheNet.com, they will not be returned.

If we wanted to select the 5 smallest contact_id values instead of the largest, we could change the sort order as follows:

 
    SELECT contact_id, last_name, first_name
    FROM contacts
    WHERE website = 'TechOnTheNet.com'
    ORDER BY contact_id ASC
    LIMIT 5;
     

Here the results would be sorted by contact_id in ascending order. So the first 5 smallest contact_id records that have a website of TechOnTheNet.com would be returned, and no other records would be returned by this query.

Limit with Offset

Though the OFFSET keyword is not supported by eXtremeSQL, the notion of the offset functionality is supported using the LIMIT expression. For example:

 
    SELECT... FROM table LIMIT M,N;
     

will select the N records starting from line number M (where M is zero-based); in other words skip M result set rows. Thus

 
    SELECT... FROM table LIMIT 0,N;
     

is equivalent to:

 
    SELECT... FROM table LIMIT N;