CODEWITHSUNDEEP
mysql
red23jordan
red23jordan

Reputation: 2891

Display detail data from mysql using group by

For example, I have a table: 

id      name       type           price
123451  Park's Great Hits   Music   19.99
123452  Silly Puddy Toy 3.99
123453  Playstation Toy 89.95
123454  Men's T-Shirt   Clothing    32.50
123455  Blouse  Clothing    34.97
123456  Electronica 2002    Music   3.99
123457  Country Tunes   Music   21.55
123458  Watermelon  Food    8.73

If I want to select the cheapest price by type, I use this : 

<?php
// Make a MySQL Connection

$query = "SELECT type, MIN(price) FROM products GROUP BY type"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo "The cheapest  ". $row['type']. " is $" .$row['MIN(price)'];
    echo "<br />";
}
?>

Then, it will display: 

The cheapest Clothing is $32.50
The cheapest Food is $8.73
The cheapest Music is $3.99
The cheapest Toy is $3.99

However, I want to know the detail information of the cheapest item so that it will display:

    The cheapest Clothing is $32.50 which is Men's T-Shirt and id is 123454
    The cheapest Food is $8.73 which is Watermelon and id is 123458
    The cheapest Music is $3.99 which is Electronica 2002 and id is 123452
    The cheapest Toy is $3.99 which is SillyPuddy and id is 123452

Please help!!

Upvotes: 0

Views: 713

Answers (2)

Chetter Hummin
Chetter Hummin

Reputation: 6817

Please try the following query

select * from products po
where price = 
(SELECT MIN(price) FROM products pi
where type = po.type)

If you want to add a time component (as wirtten in your comments), the query should be

select id, name, type, price, time from products po
where price = 
(SELECT MIN(price) FROM products pi1
   where type = po.type)
and time = 
(SELECT MIN(time) FROM products pi2
  where type = po.type
   and price = po.price)

Upvotes: 2

barsju
barsju

Reputation: 4446

If you order first then group you should get what you want:

SELECT type, price, name, id FROM products ORDER BY price DESC GROUP BY type;

Upvotes: 0

Related Questions