Overview

In this article, we will cover these PostgreSQL clauses with examples: HAVING, LIMIT and FETCH. In a previous article we discussed the WHERE, ORDER BY and GROUP BY  clauses. Click the link if you wish to learn about those clauses.

HAVING Clause

The HAVING clause works on grouped data returned by a GROUP BY. The HAVING clause has a different role to play in a SQL query from the WHERE clause; here's how:

• The HAVING clause works on a group of rows whereas the WHERE clause works on individual rows.
• Aggregate Functions, like Sum(), Min(), Max(), etc., can be used with the HAVING clause but they can't be used with the WHERE clause.
• Unlike the WHERE clause, the HAVING clause can't be used with UPDATE or DELETE statements and works only with the SELECT statement.

The basic syntax for the HAVING clause is:

SELECT col1,col2,...,colN
FROM table_name
WHERE condition
GROUP BY col1,col2,...,colN
HAVING condition

Let us see some examples of the HAVING clause in PostgreSQL. Here we will take the employees table, which we created in the earlier articles.

Example 1: Having without GROUP BY

The HAVING clause can't be used as a where clause and must be used either with a GROUP BY or aggregate function. The SQL engine throws an error when we try to execute a query with HAVING clause but without GROUP BY or aggregate function.

Example 2: HAVING with GROUP BY clause

Let us break this query in 2 parts, one up to GROUP BY followed by the HAVING clause. In the original data set, we see there are two entries for age 21, gender F.

Fig 1 - Original Table data

Next after application of GROUP BY, the data is filtered or narrowed down as per the specified condition. In this case, all data is grouped by gender and then age.

Fig 2 - GROUP BY Applied

The result shows just one entry for  'age 21, gender F', this is because the GROUP By clause is applied on columns age and gender thereby grouping similar rows into one.

Next the HAVING clause works on the grouped data. Since there is only entry which matches the condition 'age > 31' only one row is returned in the final result.

select gender,age from employees
group by gender,age
having age > 31;

Fig 3 - Final Result

Example 3: HAVING with aggregate function

To illustrate the next examples we will take another table, worldcuphistory, which we created in the earlier articles on PostgreSQL.

In this example we wish to list out the countries who have won the world cup more than once with respect to each sport. So how does the SQL work? Let us break it into two parts.

select sports_name,winner,count(winner) from worldcuphistory
group by sports_name,winner
having count(winner)>1;

In the 1st part the GROUP BY clause internally creates two groups one each for Cricket and Football with respect to the winners as shown below:

In the 2nd part, the HAVING clause tries to find out the winner from each group who have won it more than once. Therefore we see only India in the query result as it has won the Cricket world cup twice.

Note: Not to be confused with England as table shows couple of entries. England has two entries but for different sports, one each for Cricket and Football.

Example 4: Having with one group by column

Now suppose we want to see all the winners who have won the world cup more than once irrespective of sport. This can be achieved by modifying the above SQL as shown below:

select winner,count(winner) from worldcuphistory
group by winner
having count(winner)>1;

How did this work? We removed the column 'sports_name' from the query, which prompted the GROUP BY clause to create just one group based on the column 'winner'. Next, from this group the HAVING clause selected those countries who have won the world cup more than once.

The LIMIT Clause

The Limit clause is used to return a limited number of records by the select statement, in other words the number of rows returned by the SQL statement. The basic Syntax is:

SELECT col1,col2,...,colN
FROM table_name
LIMIT [number of rows]

Example 1: LIMIT NO OF ROWS

In this statement, the numeric value 2 after the 'LIMIT' clause signifies two rows need to be returned out of all the rows fetched by the select statement.

Example 2: LIMIT with OFFSET Keyword

The OFFSET keyword is used to jump a certain number of rows from the 1st row till 'Nth' row before beginning to return rows. The basic syntax is:

select * from employees LIMIT N OFFSET N

N is the number of rows. In this statement LIMIT N specifies the number of rows to be returned while OFFSET N specifies the number of rows to be ignored before beginning to return rows.

In this example the OFFSET 2 clause tells PostgreSQL engine to skip the first two rows from the beginning and then return the following two rows as asked by the LIMIT 2 clause.

select * from employees LIMIT 2 OFFSET 2

Example 3: LIMIT with OFFSET Keyword and ORDER By clause

The ORDER BY clause in SQL is used to sort the result either in ascending or descending order. Assuming we are already familiar with the use of LIMIT and OFFSET from our previous examples, the ORDER BY can be used in conjunction with the LIMIT clause and OFFSET Keyword to sort the result as shown above. Point to note, the sequence of these clauses when used together in a SQL.

select * from employees order by age desc LIMIT 2 OFFSET 2;

The FETCH Clause

The FETCH clause works similar to the LIMIT clause which is to retrieve a subset of rows from a table. Now, the obvious question that arises here is - why to use the FETCH clause when there is already a clause in PostgreSQL that behaves exactly in the same manner?

Well the answer is, the LIMIT clause doesn't conform to the ANSI SQL standard. Therefore to comply with the SQL standard, FETCH was introduced in 2008 and was made a part of SQL:2008. Functionally there is another significant difference between the two, FETCH clauses makes use of an cursor internally to retrieve the rows.

The basic syntax is:

SELECT * FROM table_name FETCH [ FIRST | NEXT ] number_of_rows [ ROW | ROWS ] ONLY;

The FETCH [ FIRST | NEXT ] involve the cursor and its associated position to return the number of rows.  FIRST and NEXT are synonyms and can be used interchangeably. The number_of_rows must be equal to or more than 1. ROW | ROWS are once again synonyms and can be used interchangeably

Example 1: FETCH FIRST, FETCH NEXT

FETCH FIRST or FETCH NEXT are synonyms and works the same way. These work with the use of a cursor. The cursor is positioned before the first row and after the query is processed it is positioned on the most recent retrieved row. This first example shows FETCH FIRST.

select * from worldcuphistory FETCH FIRST 3 ROWS ONLY;

Below we can see FETCH NEXT, where we get the same result as above.

select * from worldcuphistory FETCH NEXT 3 ROWS ONLY;

FETCH ALL or FETCH BACKWARD ALL always leaves the cursor positioned after the last row or before the first row.

Example 2: FETCH with OFFSET

In this statement the OFFSET keyword is used to ignore the number of rows before beginning to return rows from the result set. In this example given OFFSET is 4, the first 4 rows are skipped and then the following 2 rows are returned as asked by the 'FETCH FIRST  2' clause.

select * from worldcuphistory OFFSET 4 ROWS FETCH FIRST 2 ROWS ONLY;

Note, OFFSET must not be negative

Example 3: FETCH with OFFSET keyword and ORDER BY clause

The ORDER BY clause should come before the FETCH clause and OFFSET Keyword as shown below.

select * from worldcuphistory FETCH FIRST 2 ROWS ONLY OFFSET 4 ROWS;

Assuming we are already familiar with the use of FETCH and OFFSET from our previous examples, the ORDER BY can be used in conjunction with the FETCH clause and OFFSET Keyword to sort the result as shown above. Point to note, the sequence of these clauses when used together in a SQL.

Conclusion

This article gives an overview on the different aspects of HAVING,LIMIT and FETCH Clauses in PostgreSQL. We show how to use these clauses in practical world with the help of different tables. We hope this article will help you get started on your journey.