16.6 C
Jaipur
Wednesday, December 1, 2021

How Do I Find the Maximum Value of a Column in PostgreSQL?

Must read

PostgreSQL allows you to perform many functions including the built-in data types. You can use any function or operator according to what you need. The most basic used PostgreSQL functions are COUNT, MAX, MIN, AVG, SUM, ARRAY, NUMERIC and, STRING. You can use these functions suitably for retrieving the data from a created table. In this article, we will be discussing finding the maximum value of a column from a table. MAX function is a built-in function in PostgreSQL which returns the maximum value from a given set of values.

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,

# SELECT * FROM “EMPLOYEES”;

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:

# SELECT MAX(“SALARY”) FROM “EMPLOYEES”;

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:

# SELECT MAX(“SALARY”) AS “Highest Salary” FROM “EMPLOYEES”;

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:

# SELECT * FROM “EMPLOYEES” WHERE “SALARY”= (SELECT MAX(“SALARY”) FROM “EMPLOYEES”);

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:

# SELECT MAX(“AGE”) AS “MAXIMUM AGE”, MAX(“SALARY”) AS “HIGHEST SALARY” FROM “EMPLOYEES”;

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:

# SELECT “E_ID”,“FIRST_NAME”,“LAST_NAME”, MAX(“SALARY”) FROM “EMPLOYEES” GROUP BY “E_ID”, “FIRST_NAME”,“LAST_NAME”;

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:

# SELECT “E_ID”,“FIRST_NAME”, “LAST_NAME”,  MAX(“SALARY”) AS “HIGHEST SALARY”

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:

# SELECT MAX(“SALARY”) AS “HIGHEST SALARY”, MAX(“AGE”) AS “MAXIMUM AGE” FROM “EMPLOYEES” WHERE “DESIGNATION”= ‘MANAGER’;

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.

Source link

- Advertisement -

More articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement -

Latest article