MAX function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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
  • PostgreSQL MAX as a level
  • PostgreSQL MAX function two columns
  • PostgreSQL MAX WHERE clause
  • PostgreSQL MAX with GROUP BY
  • PostgreSQL MAX with HAVING CLAUSE
  • PostgreSQL MAX with GROUP BY and ORDER BY

PostgreSQL MAX function example

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

SQL

SELECT MAX(salary) 
FROM employee;

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

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

OR

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

OR

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

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

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

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

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

 

PostgreSQL MAX with GROUP BY

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

SQL

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

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)

PostgreSQL MAX with HAVING CLAUSE

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

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

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)

PostgreSQL MAX with GROUP BY and ORDER BY

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

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;

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)