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

In SQL , A database table can have multiple rows with maximum value on a column and can be have single row with maximum value . There are two ways to find and select rows with maximum value in a column.

1. Sub query with Max() Function
2. Left Outer join

 

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

The SQL query to select rows with maximum value for a specific column can be achieved using a max() function in sub query with WHERE clause.

 

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:

 

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