w3resource

MySQL: Rows holding group wise maximum

Rows holding group-wise maximum

In this page, we have shown how to retrieve the rows holding group-wise maximum for a column.

In the example shown and explained in this page, we retrieved the most expensive books of each language.

Example:

Sample table: book_mast


Code:

SELECT book_name, pub_lang, book_price
FROM book_mast b1
WHERE book_price = 
SELECT MAX( b2.book_price )
FROM book_mast b2
WHERE b1.pub_lang = b2.pub_lang ); 

Explanation:

The above MySQL statement has performed the following -

1. book_name, pub_lang, book_price are retrieved from book_mast, if

a) language of the most costly book of book_mast aliased as b2 and b1 are alike.

Sample Output:

mysql> SELECT book_name, pub_lang, book_price  
    -> FROM book_mast b1  
    -> WHERE book_price = (  
    -> SELECT MAX( b2.book_price )  
    -> FROM book_mast b2  
    -> WHERE b1.pub_lang = b2.pub_lang );
+----------------------------------+----------+------------+
| book_name                        | pub_lang | book_price |
+----------------------------------+----------+------------+
| Guide to Networking              | Hindi    |     200.00 | 
| Transfer  of Heat and Mass       | English  |     250.00 | 
| Fundamentals of Heat             | German   |     112.00 | 
| The Experimental Analysis of Cat | French   |      95.00 | 
+----------------------------------+----------+------------+
4 rows in set (0.00 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-rows-holding-group-wise-maximum-for-a-column php mysql examples | w3resource</title>
<meta name="description" content="PHP MySQL PDO Example">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of most expensive books of each language : </h2>
<table class='table table-bordered'>
<tr>
<th>Book</th><th>Language</th><th>Price</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT book_name, pub_lang, book_price
FROM book_mast b1
WHERE book_price = (
SELECT MAX( b2.book_price )
FROM book_mast b2
WHERE b1.pub_lang = b2.pub_lang )') as $row) {
echo "<tr>"; 
echo "<td>" . $row['book_name'] . "</td>";
echo "<td>" . $row['pub_lang'] . "</td>";
echo "<td>" . $row['book_price'] . "</td>"; 
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:


Previous: Find duplicate data in MySQL
Next: Date Calculation



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql/advance-query-in-mysql/rows-holding-group-wise-maximum-for-a-column.php