CODEWITHSUNDEEP
mysqlsql
Hativ
Hativ

Reputation: 1530

MySQL: Get the full row with min value

I am trying to get the complete row with the lowest price, not just the field with the lowest price.

Create table:

CREATE TABLE `Products` (
  `SubProduct` varchar(100),
  `Product` varchar(100),
  `Feature1` varchar(100),
  `Feature2` varchar(100),
  `Feature3` varchar(100),
  `Price1` float,
  `Price2` float,
  `Price3` float,
  `Supplier` varchar(100)
);

Insert:

INSERT INTO
  `Products` (`SubProduct`, `Product`, `Feature1`, `Feature2`, `Feature3`, `Price1`, `Price2`, `Price3`, `Supplier`)
VALUES
  ('Awesome', 'Product', 'foo', 'foo', 'foor', '1.50', '1.50', '0', 'supplier1'),
  ('Awesome', 'Product', 'bar', 'foo', 'bar', '1.25', '1.75', '0', 'supplier2');

Select:

SELECT
  `SubProduct`,
  `Product`,
  `Feature1`,
  `Feature2`,
  `Feature3`,
  MIN(`Price1`),
  `Price2`,
  `Price3`,
  `Supplier`
FROM `Products`
  GROUP BY `SubProduct`, `Product`
  ORDER BY `SubProduct`, `Product`;

You can see that at http://sqlfiddle.com/#!2/c0543/1/0

I get the frist inserted row with the content of the column price1 from the second inserted row.

I expect to get the complete row with the right features, supplier and other columns. In this example it should be the complete second inserted row, because it has the lowest price in column price1.

Upvotes: 0

Views: 702

Answers (3)

smoore4
smoore4

Reputation: 4866

This works...

SELECT
P1.`SubProduct`,
P1.`Product`,
P1.`Feature1`,
P1.`Feature2`,
P1.`Feature3`,
P1.`Price1`,
P1.`Price2`,
P1.`Price3`,
P1.`Supplier`
FROM `Products` P1
INNER JOIN `Products` P2 ON  P1.SubProduct = P2.SubProduct AND P1.Product = P2.Product
WHERE P1.Price1 < P2.Price1

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You need to get the MIN price rows and then JOIN those rows with the main table, like this:

SELECT
  P.`SubProduct`,
  P.`Product`,
  P.`Feature1`,
  P.`Feature2`,
  P.`Feature3`,
  `Price` AS Price1,
  P.`Price2`,
  P.`Price3`,
  P.`Supplier`
FROM `Products` AS P JOIN (
    SELECT `SubProduct`, `Product`, MIN(`Price1`) AS Price
    FROM `Products`
    GROUP BY `SubProduct`, `Product`
  ) AS `MinPriceRows`
ON P.`SubProduct` = MinPriceRows.`SubProduct`
AND P.`Product` = MinPriceRows.`Product`
AND P.Price1 = MinPriceRows.Price
ORDER BY P.`SubProduct`, P.`Product`;

Working Demo: http://sqlfiddle.com/#!2/c0543/20

Here what I have done is to get a temporary recordset as MinPriceRows table which will give you MIN price per SubProduct and Product. Then I am joining these rows with the main table so that main table rows can be reduced to only those rows which contain MIN price per SubProduct and Product.

Upvotes: 2

Milen
Milen

Reputation: 8867

Try with this:

SELECT
  `p`.`SubProduct`,
  `p`.`Product`,
  `p`.`Feature1`,
  `p`.`Feature2`,
  `p`.`Feature3`,
 `p`.`Price1`,
  `p`.`Price2`,
  `p`.`Price3`,
  `p`.`Supplier`
FROM `Products` `p`
inner join (select MIN(`Price1`)as `Price1`
            From `Products`
           ) `a` on `a`.`Price1` = `p`.`Price1`

ORDER BY `p`.`SubProduct`, `p`.`Product`;

demo: http://sqlfiddle.com/#!2/c0543/24

Upvotes: 1

Related Questions