CODEWITHSUNDEEP
phpsqlselectgroup-by
Jurgen
Jurgen

Reputation: 274

PHP SQL selecting and grouping with no duplicates

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;

Upvotes: 0

Views: 60

Answers (1)

mrodo
mrodo

Reputation: 582

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;

Upvotes: 1

Related Questions