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:
- 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. - 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.
- 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. - Using the RANK() or DENSE_RANK() window functions: Similar to
ROW_NUMBER()
,RANK()
andDENSE_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. - 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.
- 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.
- 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