PHP SQL selecting and grouping with no duplicates-CodePudding

I would like to return no duplicate ISBN having the lowest price (see rows in bold). How can I accomplish that?

isbn price supplier

4000 22.50 companyA

4000 19.99 companyB

4000 22.50 companyC

4001 33.50 companyA

4001 45.50 companyB

4003 11.99 companyB

My query is designed to use OR operators. That would be welcome to keep this method.

SELECT * FROM table WHERE isbn = 4000 OR isbn = 4001 OR isbn = 4003 GROUP BY isbn ORDER BY price;

CodePudding user response：

You just need to use the MIN aggregate function:

SELECT isbn, MIN(price)
FROM table 
WHERE isbn = 4000 OR isbn = 4001 OR isbn = 4003 
GROUP BY isbn 
ORDER BY price;

Also, as the comment pointed out, using IN is probably better for your case than a series of OR:

SELECT isbn, MIN(price)
FROM table 
WHERE isbn IN (4000, 4001, 4003)
GROUP BY isbn 
ORDER BY price;

CodePudding user response：

You can also do like this

SELECT DISTINCT tbl.*
FROM tbl tbl
  INNER JOIN
  (
     SELECT isbn,
       MIN(price) as Price
    from tbl
    group by isbn
  ) tbl1
  ON tbl1.isbn = tbl.isbn
  where tbl.isbn = tbl1.isbn and tbl.price = tbl1.Price
  order by isbn