How to Select Row With Max Value in SQL Server

In SQL Server, retrieving rows that contain the maximum value for a specific column for each distinct value in another column can be a common and challenging task. This process is done by identifying the maximum value for each group and then selecting the corresponding rows. In this article, we’ll explore how to achieve this using SQL Server’s features, providing clarity and efficiency in fetching such records.

How to Get the Max Value for Each Column?

MAX() Function is a function that is used to return the maximum value from 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:

1. Using SELF-JOIN
2. USING WHERE CLAUSE

let’s Setting Up the 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.

Query:

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:

Output

1. Using SELF JOIN

A self-join perform an operation when a table is joined with itself. To solve the problem presented to us, 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:

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:

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.

More Technical Example

Let’s create the table and insert some data inside it. The following query creates a sale_record table and inserts several records in it.

Query:

CREATE TABLE sale_record (
  id INTEGER PRIMARY KEY,
  item VARCHAR(50),
  quantity INT,
  cost FLOAT,
  sale_date DATE
);

INSERT INTO sale_record VALUES (1, 'Pen', 2, 5, '2024-01-21');
INSERT INTO sale_record VALUES (2, 'Chart Paper', 4, 25, '2024-02-12');
INSERT INTO sale_record VALUES (3, 'Notebook', 10, 10, '2024-01-31');
INSERT INTO sale_record VALUES (4, 'Chart Paper', 3, 35, '2024-01-15');
INSERT INTO sale_record VALUES (5, 'Pen', 12, 2, '2024-01-22');
INSERT INTO sale_record VALUES (6, 'Chart Paper', 10, 20, '2024-01-21');
INSERT INTO sale_record VALUES (7, 'Notebook', 5, 10, '2024-02-09');
INSERT INTO sale_record VALUES (8, 'Notebook', 1, 200, '2024-01-25');
INSERT INTO sale_record VALUES (9, 'Pen', 20, 2, '2024-01-08');
INSERT INTO sale_record VALUES (10, 'Eraser', 2, 8, '2024-01-17');
INSERT INTO sale_record VALUES (11, 'Pen', 25, 3, '2024-02-11');
INSERT INTO sale_record VALUES (12, 'Eraser', 1, 15, '2024-01-21');

Output:

Output

Explanation: As we can see the above table stores records of how much units and at what cost of different items were sold at different days.

We will now use the methods we have learned in this article, to find the maximum profit earned for each product and at what date. For the case of simplicity, we will define profit to be the product of quantity and cost.

We will make use of SELF-JOIN method to solve this. First we will create the modified version of the table. In this we will group the records by the item and then calculate the maximum profit for each item. We will later join this modified table with the original table to find the relevant records. The following is the query.

Query:

SELECT sr.id, sr.item, sr.quantity, sr.cost, (sr.quantity*sr.cost) profit, sr.sale_date FROM sale_record sr
JOIN (
  SELECT item, MAX(quantity*cost) as max_profit
  FROM sale_record
  GROUP BY item
) md
ON sr.item=md.item
AND sr.quantity*sr.cost=md.max_profit;

Output:

Output

Explanation: This query retrieves records from the “sale_record” table where the calculated profit (quantity * cost) for each item is the maximum profit for that item. It uses a subquery to calculate the maximum profit for each item and then joins the original table with this subquery to filter the rows with the maximum profit for each item.

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 SQL Server. 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.