Postgres/MySQL – How to Show A Specific Range of Rows

In Postgres and MySQL database, there is a neat functionality which returns results from a specific range of rows.

This is very useful if  you have a database with a large number of rows and have an interface which displays only x numbers of rows in a view with the functionality to view the ‘second’, ‘third’ page of results and so forth. Rather than having to write code to parse through the entire result set and determine which to display, you can have postgres or SQL do that work for you.

This is done using LIMIT and OFFSET.

The examples below demonstrate how they works.

Get the top 10 rows of the results.

SELECT * FROM TABLE_NAME LIMIT 10;

Get rows 20 to 30 of the results.

SELECT * FROM TABLE_NAME LIMIT 10 OFFSET 20;