PostgreSQL – MAX Function

MAX function is an aggregate function that allows you to get the maximum of values.

Example:

Consider the employee table as shown below:

idfirst_namelast_namedepartmentsalary
101bryanwalkersales50,000
102alexcarpenteraccounts55,000
103samwhitesales60,000
104johnbarrysales54,000
105billwoodaccounts65,000
106roberthopkinssupport70,000
107davidleesupport72,000
108jacksearssupport65,000
109tomyoungsales55,000
employee

You can get the maximum of all salaries as shown below:

SELECT MAX(salary) FROM employee;

sum
------
72,000

You can use MAX function along with the group by clause. Example: To return maximum of salaries per department, you can group by department as shown below:

SELECT department, MAX(salary) FROM employee GROUP BY department;

department  sum
----------  ---
sales       60,000
accounts    65,000
support     72,000

You can filter grouped results using HAVING clause. Example: Below query returns only the departments that are paying a maximum salary of more than 60,000.

SELECT department, MAX(salary) FROM employee GROUP BY department HAVING MAX(salary)>60000;

department  sum
----------  ---
accounts    65,000
support     72,000
See also  PostgreSQL - ERROR: permission denied for relation