Syntax
The syntax for the MAX function is expressed as:
Different Ways of Using MAX Function in PostgreSQL
You can use MAX functions for various uses, for example, if you want to know the highest marks a student has achieved in a class, then by simply using the MAX function, you can retrieve the data of those students who have the greatest marks in a column.
For using the MAX function in PostgreSQL, you first must create a table and insert some data in it, this way you can get the MAX value from a set of values. There are two ways for creating a table through SQL Shell or pgAdmin, we have created a table through pgAdmin. Once you have created a table MAX function can be used in different ways to return the given set of values.
We have created a sample table EMPLOYEES, run this query for viewing the table,
The created table can be seen in the cited image.
In this “EMPLOYEES” table, seven columns are inserted which include E_ID, FIRST_NAME, LAST_NAME, EMAIL, AGE, SALARY, DESIGNATION. We want to know the highest salary in this table, for that we will run the following query:
In this query, the “MAX” function will go to the “EMPLOYEES” table then the “SALARY” column will return the maximum salary from that column. The result would be shown on the console with the “SELECT” statement as:
MAX Function with ‘AS’ in PostgreSQL
If a user wants to find the highest salary with the name head “Highest Salary” from the “EMPLOYEES” table, then run this query:
The “AS” keyword in the above query is used to create the name of a column or table as the desired alias name. In the above query, the “SALARY” column result heading is changed to a temporary alias name as “Highest Salary” so that we can clearly understand the purpose of using a “MAX” function. The desired output would be displayed as shown in the image below:
The output would be displayed as “max” if we didn’t use the “AS” keyword in the query, but it is displayed as “Highest Salary” in the data output.
MAX Function Using Subquery in PostgreSQL
If we want to display all columns data who have the highest salary in the table, we will use the following query:
In this query, the subquery will be executing first where the highest salary is returning then the outer query will be selecting all rows that have the highest salary in the table. The final output would be displaying all employees having maximum salary in the “EMPLOYEES” table as shown in the image below:
MAX Function with Two Columns in PostgreSQL
Now, we want two maximum values from a table in different columns. For that, we will be using the following query:
The above query first finds the maximum age from the column “AGE” and displays it as “MAXIMUM AGE”, then it moves to the second column “SALARY” and displays it as “HIGHEST SALARY” in the output with the help of “SELECT” statement the following result would be seen on the console:
The result shows the “MAXIMUM AGE” and the “HIGHEST SALARY” of the employee in their respective columns.
MAX Function Using GROUP BY Clause in PostgreSQL
The “GROUP BY” clause allows you to group the values from a database having similar values. It summarizes your data of a column. The query for using the “GROUP BY” clause is:
In this query, by using the “MAX” function with the “GROUP BY” clause we have displayed the maximum values of each group for their E_ID, FIRST_NAME, LAST_NAME, and SALARY. The output for this query is shown as:
MAX Function Using HAVING Clause in PostgreSQL
The “HAVING” clause returns the rows which meet the defined conditions in a statement and filters out all the other rows which don’t meet the conditions and displays them in output. It fetches the specified rows and meets the conditions mentioned. The use of the “HAVING” clause in PostgreSQL in a query is as follow:
FROM “EMPLOYEES” GROUP BY “E_ID” HAVING MAX(“SALARY”)>30000;
This query filters out all the salaries of employees that are less than or equal to 30,000 and displays the E_ID, FIRST_NAME, LAST_NAME, SALARY columns in the data output. You can also set the value of your choice according to your specified conditions. The output for this query is shown as:
MAX Function Using Where Clause in PostgreSQL
If you want to display only desired records in a table, you can specify conditions using the “WHERE” clause in a query. The “WHERE” clause will fetch specific values from a table according to the conditions you have set and display them in output. For using the “WHERE” clause in PostgreSQL, run the following query:
In this query, we have specified a condition that retrieves the record of that employee whose “DESIGNATION” is Manager and selects the maximum salary and age of the employee from the “EMPLOYEES” table. This query displays the maximum salary and age from the “EMPLOYEES” table whose “DESIGNATION” is ‘MANAGER’.
Conclusion
In this article, we have discussed how you can find a maximum value of a column in PostgreSQL using different examples and functions. First, you must create a table and insert some values in it so that the “MAX” function can return some values from some columns. A few simple examples are implemented on PostgreSQL for getting the maximum value from a table then, some different ways of using the “MAX” function in PostgreSQL are mentioned and implemented with the desired output for better understanding. You can use the “MAX” function using Subqueries, “AS” Keyword, “GROUP BY” clause, “HAVING” clause, “WHERE” clause, and by combining two columns as well.
All these ways of using the “MAX” function depend on what output you want to display. You can also create your table in PostgreSQL and run different queries mentioned in the article for the “MAX” function using different methods.
