PostgreSQL MAX function

MAX function

The PostgreSQL MAX function returns the maximum value, specified by expression in a set of aggregated rows. This function accepts an expression including any numeric, string, date, or time data type values and returns the maximum as a value of the same data type as specified in the expression .

Syntax:

MAX (* | [DISTINCT] ALL | column_name)

Parameters

PostgreSQL MAX function example

N.B. The DISTINCT and ALL have no effect since the maximum value would be same in either case.

The sample table

If we want to find the maximum salary from all employees in the employee table, the following SQL can be used.

SQL

Code:

SELECT MAX(salary) 
FROM employee;

Output:

Pictorial Presentation of PostgreSQL MAX() function

PostgreSQL MAX as a level

If we want to find the maximum salary from all employees and show the result against 'Maximum Salary' head in the employee table, the following SQL can be used.

SQL

Code:

SELECT MAX(salary)  AS "Maximum Salary"
FROM employee;

OR

Code:

SELECT MAX(DISTINCT salary)  AS "Maximum Salary"
FROM employee;

OR

Code:

SELECT MAX(ALL salary)  AS "Maximum Salary"
FROM employee;

Output:

PostgreSQL MAX function two columns

If we want to get the maximum salary and commission from employee table, the following SQL can be used.

SQL

Code:

SELECT MAX(salary) "Maximum Salary",
MAX(commission) "Maximum Commission" 
FROM employee;

Output:

PostgreSQL MAX WHERE clause

If we want to get the maximum salary and deduction from employee table whose designation is CLERCK, the following SQL can be used.

SQL

Code:

SELECT MAX(salary)AS "Maximum Salary",
MAX(deduction) AS "Maximum Deduction"
FROM employee
WHERE designame='CLERCK';

Output:

PostgreSQL MAX with GROUP BY

Sample table: employees

If we want to get the maximum salary for each designation available in employees table, the following SQL can be used.

SQL

Code:

SELECT job_id, MAX(salary) AS "Maximum Salary" 
FROM employees 
GROUP BY job_id;

Sample Output:

   job_id   | Maximum Salary
------------+----------------
 AC_ACCOUNT |        8300.00
 ST_MAN     |        8200.00
 IT_PROG    |        9000.00
 SA_MAN     |       14000.00
 AD_PRES    |       24000.00
 AC_MGR     |       12000.00
 FI_MGR     |       12000.00
 AD_ASST    |        4400.00
 MK_MAN     |       13000.00
 PU_CLERK   |        3100.00
 HR_REP     |        6500.00
 PR_REP     |       10000.00
 FI_ACCOUNT |        9000.00
 SH_CLERK   |        4200.00
 AD_VP      |       17000.00
 SA_REP     |       11500.00
 ST_CLERK   |        3600.00
 MK_REP     |        6000.00
 PU_MAN     |       11000.00
(19 rows)

Pictorial Presentation of PostgreSQL MAX with GROUP BY

PostgreSQL MAX with HAVING CLAUSE

Sample table: employees

If we want to get those designations, whose maximum salary is 6500 and above within the salary range below 12000, the following SQL can be used.

SQL

Code:

SELECT job_id,MAX(salary) AS "Maximum Salary" 
FROM employees 
WHERE salary<12000 
GROUP BY job_id
HAVING MAX(salary)>=6500;

Sample Output:

   job_id   | Maximum Salary
------------+----------------
 AC_ACCOUNT |        8300.00
 ST_MAN     |        8200.00
 IT_PROG    |        9000.00
 SA_MAN     |       11000.00
 HR_REP     |        6500.00
 PR_REP     |       10000.00
 FI_ACCOUNT |        9000.00
 SA_REP     |       11500.00
 PU_MAN     |       11000.00
(9 rows)

Pictorial Presentation of PostgreSQL MAX with HAVING

PostgreSQL MAX with GROUP BY and ORDER BY

Sample table: employees

The following query will return the designation which maximum salary is 6500 and above within the salary range below 12000 and the maximum salary for each designation comes in the list in descending order.

SQL

Code:

SELECT job_id,MAX(salary) AS "Maximum Salary" 
FROM employees 
WHERE salary<12000
GROUP BY job_id 
HAVING MAX(salary)>=6500
ORDER BY MAX(salary) DESC;

Sample Output:

   job_id   | Maximum Salary
------------+----------------
 SA_REP     |       11500.00
 PU_MAN     |       11000.00
 SA_MAN     |       11000.00
 PR_REP     |       10000.00
 FI_ACCOUNT |        9000.00
 IT_PROG    |        9000.00
 AC_ACCOUNT |        8300.00
 ST_MAN     |        8200.00
 HR_REP     |        6500.00
(9 rows)

Previous: SUM
Next: MIN