LIMIT Command

There are situations where you don't need all the results in the database - you just need a few. Say you need the names of the first 5 characters in the database. If you run a SELECT query, you will get the entire table. But if you use the LIMIT option you can limit the number of results. To get the just 5 characters, the command would be...

SELECT * FROM Character LIMIT 0,5

Syntax of LIMIT Command

SELECT <fields> FROM <table> LIMIT <Offset>,<Number of Results>

Offset: The offset is the number of the row from where the results start. If the offset is 0, the results are fetched from the beginning of the table. If offset is 1, then the first row is ignored - the fetching is started at the second row. If offset is 5, the first 5 rows are skipped.
Number of Results: This should be self-explanatory. This specifies the number of rows that should be fetched.

Uses

LIMIT has a lot of uses. For example, say you want the name of the character with the second highest IQ. Try this query...

SELECT Name FROM Character ORDER BY iq DESC LIMIT 1,1

Pagination

LIMIT is most often used in pagination. Pagination is a design pattern that lets you split up a big number of results into multiple pages. The best example I can give you is the Google results page. At the end of each result page, there is a link to the next page where there are more results. This can be implemented using LIMIT...

First Page

SELECT Character.name, Quote.quote FROM Quote INNER JOIN Character ON Quote.character_id=Character.id LIMIT 0,5

Second Page

SELECT Character.name, Quote.quote FROM Quote INNER JOIN Character ON Quote.character_id=Character.id LIMIT 5,5

Third Page

SELECT Character.name, Quote.quote FROM Quote INNER JOIN Character ON Quote.character_id=Character.id LIMIT 10,5

Previous
Sorting the Results using ORDER BY

Next
Group Multiple Rows using GROUP BY

Contents