LIMIT and OFFSET provide the capability to retrieve only a portion of the generated result from the query. If a limit count is specified, the number of rows returned correspond to that limit count. If an offset count is given, then that number is the amount of rows skipped before they begin to be returned. The syntax for limit and offset are:

  SELECT select_list FROM table_expression 
  [ORDER BY] sort_spec][LIMIT {number | ALL}]
  [OFFSET number]

	Note
	When using LIMIT, an ORDER BY clause can be extremely beneficial to obtaining relevant results, because the sorting occurs before the limiting. In other words, the ORDER BY clause will help you prepare the most appropriate range of data to be limited before the LIMIT clause is processed.

This uses the book table to display only 3 rows of titles and author identification numbers ordered according to the title names:

Example 4-25. Using the LIMIT Clause

  SELECT title, author_id
    FROM book 
   ORDER BY title
   LIMIT 3;

The output of this query is:

            title            | author_id
-----------------------------+-----------
 Bartholomew and the Oobleck |       110
 Franklin in the Dark        |       142
 Goodnight Moon              |       136
(3 rows) 

The following limit the number of rows returned, and start from an offset of 2. It will return rows formatted by the date and display the author identification numbers and copyright dates.

Example 4-26. The LIMIT and OFFSET

  SELECT author_id, cpright_date
    FROM book 
ORDER BY cpright_date
   LIMIT 4
  OFFSET 2;       
  

The resulting query looks like this:

 author_id | cpright_date
-----------+--------------
       231 | 1980-02-15
       142 | 1986-09-22
       136 | 1991-04-01
       110 | 1997-02-01y
(4 rows)