How to select row with max value in SQL? [SOLVED]

Structured Query Language, or SQL, is the de facto language for managing and querying relational databases. One common task that arises when working with databases is the need to find the row with the maximum value for a specific column. This process can be crucial in various scenarios, such as identifying the highest-grossing product or the most recent transaction.

In this guide, we will walk you through the process of selecting the row with the maximum value for a particular column in an SQL database. We will cover various techniques and best practices, ranging from simple to more advanced approaches, to help you efficiently retrieve the desired information. By the end of this tutorial, you will be well-equipped to tackle this task, regardless of your current SQL proficiency.

 

Different methods to select row with maximum value in SQL

There are several ways to select a row with the maximum value for a specific column in SQL. Each approach has its own advantages and trade-offs, and the best method depends on your specific use case and the database management system you are working with. Here are some common techniques:

1. Using the MAX() function with a subquery: This approach involves using the aggregate function MAX() in a subquery to first find the maximum value for the desired column, and then using the result to filter and retrieve the corresponding row in the main query.
2. Using the INNER JOIN with a subquery: Similar to the first method, this approach uses a subquery to find the maximum value for the column. Instead of filtering the main query, however, you can use an INNER JOIN to combine the results and retrieve the desired row.
3. Using the ROW_NUMBER() window function: The ROW_NUMBER() function assigns a unique number to each row within a result set based on a specified column order. By ordering the column in descending order and assigning row numbers, you can filter the results to retrieve the row with the maximum value.
4. Using the RANK() or DENSE_RANK() window functions: Similar to ROW_NUMBER(), RANK() and DENSE_RANK() are window functions that assign a unique rank to each row within a result set. You can use these functions to find the row with the highest rank (i.e., the maximum value) for the specified column.
5. Using the LIMIT (or TOP) clause: In some database systems, such as MySQL and PostgreSQL, you can use the LIMIT clause to retrieve a specified number of rows from the result set. Similarly, the TOP clause is available in SQL Server. By combining this with an ORDER BY clause in descending order, you can easily retrieve the row with the maximum value.
6. Using the FETCH FIRST (or FETCH NEXT) clause: This method is available in some databases, like Oracle, DB2, and SQL Server, and works similarly to the LIMIT or TOP clause. By combining FETCH FIRST (or FETCH NEXT) with an ORDER BY clause in descending order, you can select the row with the maximum value.
7. Using LEFT OUTER JOIN with self-join: This approach involves performing a self-join on the table using a LEFT OUTER JOIN, where the condition compares the desired column values, and then filtering the result set to retrieve rows with NULL values in the right table, which represent the row with the maximum value.

 

Setup Lab Environment

Consider hospital management database with four tables patient,doctor,bill and laboratory to perform practical examples

Patient table

Doctor table

Bill table

Laboratory table

 

Method-1: Using sub query with max() function

Find the maximum bill amount using the MAX() function in a subquery, and then use the result to filter and retrieve the corresponding patient details in the main query.

Syntax

SELECT column1, column2, ..., column_n
FROM table_name
WHERE column_x = (SELECT MAX(column_max) FROM table_name);

Here,

• table_name:  is the name of the table where the data resides.
• column1, column2, ..., column_n : are the columns to be selected.
• column_max :  is the column to find the maximum value for.
• The subquery (SELECT MAX(column_x) FROM table_name) :  finds the maximum value of column_max.

The WHERE clause filters the rows to only return those that have the maximum value of column_max.

 

Example-1: Select the rows from single table having the maximum value on a column

Write SQL query to retrieve doctor information whose age is maximum

SELECT  doctor_id, name, age, gender, address
FROM   doctor
WHERE  (age =(SELECT  MAX(age) AS 'Maximum Age'
FROM  doctor AS doctor_1));

• In the above query, The selected rows using SQL select statement are filtered using a condition in the WHERE clause that compares the age of the doctor to the maximum age of all doctors in the doctor table using a subquery.
• The subquery (SELECT MAX(age) AS 'Maximum Age' FROM doctor AS doctor_1) returns the maximum age value from the "doctor" table, which is then used in the main query to find the doctor with the highest age.
• The result of this query will be the record(s) of the doctor(s) who have the maximum age in the "doctor" table.

OUTPUT:

 

Example-2: Select the rows from multiple tables having the maximum value on a column

Write SQL query to retrieve patient information who has paid maximum room charges

SELECT  bill.bill_no, bill.patient_id, patient.name, patient.age, patient.gender, bill.doctor_charge, bill.room_charge, bill.no_of_days 
FROM  bill INNER JOIN
patient ON bill.patient_id = patient.patient_id
WHERE (bill.room_charge = (SELECT   MAX(room_charge) AS 'Maximum room charges' FROM   bill AS bill_1));

• In the above query, the maximum "room_charge" value is calculated using a sub-query in the "WHERE" clause, which selects the maximum "room_charge" value from the "bill" table.
• The query includes a "WHERE" clause that filters the results to only those records in the "bill" table whose "room_charge" is equal to the maximum "room_charge" in the entire "bill" table.
• The query is fetching data from two tables, "bill" and "patient", and combining them using an "INNER JOIN" based on the common field "patient_id".

OUTPUT:

 

Method-2: Using Left Outer Join

SQL left outer join can also be used to find or select rows having maximum value in specified column. This can be achieved by performing left outer join with the same table. It will match for the value of a specified column in the same table for all rows and find maximum value.

Syntax

SELECT column1, column2, ..., column_n
FROM   table1 as alias_name_table1  LEFT OUTER JOIN
table2 AS alias_name_table2 ON  alias_name_table1 .column_max < alias_name_table2.column_max 
WHERE (alias_name_table2 .comman_column IS NULL);

table1 and table2 both are the same table name for perform left outer join with the same table

 

Example-1: Use SQL Left outer join to select the rows having the maximum value on a column

Write SQL query to retrieve doctor information whose age is maximum using left outer join

SELECT   d1.doctor_id, d1.name, d1.age FROM   doctor AS d1 LEFT OUTER JOIN
doctor AS d2 ON d1.age < d2.age
WHERE  (d2.doctor_id IS NULL);

• In the above query, The left outer join is used find the doctors in the "doctor" table who have the maximum age as there are no other doctors with an age greater than them.
• This query is fetching data from the "doctor" table and performing a "LEFT OUTER JOIN" with the same "doctor" table (aliased as "d2") on the condition "d1.age < d2.age".
• The "LEFT OUTER JOIN" returns all records from the left table (d1), and the matched records from the right table (d2). In case there is no match, NULL values will be returned for the columns of the right table.
• The "WHERE" clause filters the results further to only those records where the "doctor_id" of "d2" is NULL, meaning there is no match in the "d2" table.

OUTPUT:

 

Method-3: Using INNER JOIN with a subquery

Find the maximum bill amount using a subquery, and then use an INNER JOIN to combine the results and retrieve the desired patient details.

Syntax:

SELECT column1, column2, ..., column_n
FROM table_name1
JOIN table_name2 ON table_name1.column1 = table_name2.column1
JOIN (SELECT MAX(column_max) AS max_value FROM table_name) MaxValue ON table_name2.column_x = MaxValue.max_value;

Here,

• table_name1, table_name2: are the names of the tables where the data resides.
• column1, column2, ..., column_n: are the columns to be selected.
• column_max: is the column to find the maximum value for.
• The subquery (SELECT MAX(column_max) FROM table_name): finds the maximum value of column_max.
• The JOIN clause combines the rows of table_name1 and table_name2 based on the specified condition and filters the rows to only return those that have the maximum value of column_max.

 

Example-1: Select the rows from multiple tables having the maximum value on a column Write SQL query to retrieve patient information with the highest bill amount.

SELECT P.*
FROM Patient P
JOIN Bill B ON P.patient_id = B.patient_id
JOIN (SELECT MAX(amount) AS max_amount FROM Bill) MaxBill ON B.amount = MaxBill.max_amount;

In the above query, the selected rows are filtered using a condition in the JOIN clause that compares the bill amount to the maximum bill amount in the Bill table using a subquery. The subquery (SELECT MAX(amount) FROM Bill) returns the maximum bill amount, which is then used in the main query to find the

 

Summary

Our article on SQL select only rows with a maximum value on a column provides an overview of how to retrieve rows with maximum values in a specified column, as well as examples of how to find rows with maximum values: first, a subquery using SQL's max() function, and second, a left outer join using SQL. Both methods are explained along with syntax and practical examples.

 

References

SQL max() function
SQL left outer join

 

Further Reading

select only rows with max value on a column
SQL select only rows with max value on a column