Table of Contents
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.
- Sub query with
Max()
Function - 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
Further Reading
select only rows with max value on a column
SQL select only rows with max value on a column