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:
- Using SELF-JOIN
- 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
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.