w3resource

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

Name Description
column_name Name of the column
* The asterisk(*) indicates all the rows.
DISTINCT This clause is optional. It indicates uniqueness.
ALL This clause is optional. It is default clause.

Contents:

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

postgresql sample table employee example1

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:

postgresql max function example

Pictorial Presentation of PostgreSQL MAX() function

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 as a level

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 function two columns

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 WHERE clause

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 function 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 function 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



Follow us on Facebook and Twitter for latest update.