How to Select Row With Max Value in PostgreSQL

In PostgreSQL, efficiently selecting rows with maximum values from a table is a common task faced by developers and database administrators. Whether you're working on analytics, reporting, or data manipulation, knowing how to retrieve the maximum value per group can significantly enhance your SQL skills. In this article, we'll explore three powerful methods to accomplish this task, empowering you to optimize your PostgreSQL queries and streamline your data workflows.

How to Select Row With Max Value in PostgreSQL?

MAX() Function is used to return the maximum value of a set of values. It can be used with a single column or with multiple columns. To get the maximum value for each column in a table, we can use the MAX() function along with below methods which are given below:

• Method 1: Using SELF-JOIN
  
• Method 2: USING WHERE CLAUSE

Setting Up Environment

For better understanding, we need a table on which we will perform various operations and queries. The below query creates a table and then inserts some records in it.

CREATE TABLE test (
  id INTEGER PRIMARY KEY,
  val1 VARCHAR(20),
  val2 INTEGER
);

INSERT INTO test VALUES (21, 'val1', 32);
INSERT INTO test VALUES (11, 'val2', 90);
INSERT INTO test VALUES (90, 'val1', 18);
INSERT INTO test VALUES (77, 'val1', 65);
INSERT INTO test VALUES (43, 'val3', 20);
INSERT INTO test VALUES (81, 'val3', 88);
INSERT INTO test VALUES (29, 'val2', 72);
INSERT INTO test VALUES (55, 'val2', 47);
INSERT INTO test VALUES (72, 'val3', 11);

Output:

1. Using SELF JOIN

A self-join perform operation when a table is joined with itself we will join the original table with a little modified version of the table. In the modified version, we will group the table by val1 column and then use the MAX() function to find the maximum value of val2 for each group. We will later join this with the original table on the values of t1.val1=t2.val1 and t1.val2=t2.max_val2.

Query:

SELECT t1.id, t1.val1, t1.val2 FROM test t1
JOIN (
  SELECT val1, MAX(val2) AS max_val2
  FROM test
  GROUP BY val1
) t2
ON t1.val1=t2.val1 AND t1.val2=t2.max_val2;

Output:

Explanation: This query retrieves rows from the "test" table where the value in the "val2" column is the maximum for each distinct value in the "val1" column. It achieves this by using a subquery to find the maximum "val2" for each "val1" group and then joining the result back to the original table to filter the rows accordingly.

2. USING WHERE CLAUSE

We can make use of WHERE clause along with a subquery to find the max value. Just like in method 1, we will create a modified version of the table in which we group the data and find the maximum value for each group. Just the difference in this method is that rather than using JOIN, we will use WHERE to compare the value in the original table with the modified table.

Query:

SELECT t1.id, t1.val1, t1.val2 FROM test t1, (
  SELECT val1, MAX(val2) AS max_val2
  FROM test
  GROUP BY val1
) t2
WHERE t1.val1=t2.val1 AND t1.val2=t2.max_val2;

Output:

Explanation: This query also retrieves rows from the "test" table where the value in the "val2" column is the maximum for each distinct value in the "val1" column. However, it achieves this using a WHERE clause instead of a JOIN.

Conclusion

In this article, we covered how we can find the records which have the maximum value for a column for each distinct value of another column in PostgreSQL. We had a chance to look at two different methods to go about doing this, first using SELF-JOIN and later looked at how we can achieve the same using WHERE clause. We also how we can use the concepts we learned in this article to a real-life situation through the technical example.