impala having clause

The Have clause in Impala allows you to specify which groups of results are filtered to show the conditions in the final result.

In general, the Having clause is used with the group by clause; I t places the condition on a group created by the GROUP BY clause.

Grammar

Here's Howingclause's syntax.

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

Cases

Suppose we have my_db table called customers in the database database, which reads as follows -

[quickstart.cloudera:21000] > select * from customers; 
Query: select * from customers 
+----+----------+-----+-------------+--------+ 
| id | name     | age | address     | salary | 
+----+----------+-----+-------------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad   | 20000  |
| 2  | Khilan   | 25  | Delhi       | 15000  | 
| 3  | kaushik  | 23  | Kota        | 30000  | 
| 4  | Chaitali | 25  | Mumbai      | 35000  | 
| 5  | Hardik   | 27  | Bhopal      | 40000  | 
| 6  | Komal    | 22  | MP          | 32000  | 
| 7  | ram      | 25  | chennai     | 23000  | 
| 8  | rahim    | 22  | vizag       | 31000  | 
| 9  | robert   | 23  | banglore    | 28000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.51s

Here's an example of using the Haven clause in Impala -

[quickstart.cloudera:21000] > select max(salary) from customers group by age having max(salary) > 20000;

This query initially groups the tables by age, selects the maximum salary for each group, and displays wages greater than 20,000, as shown below.

20000 
+-------------+ 
| max(salary) |
+-------------+ 
| 30000       |
| 35000       | 
| 40000       | 
| 32000       | 
+-------------+ 
Fetched 4 row(s) in 1.30s