View Full Version : Cheapest Price per Client

09-06-2006, 03:29 PM
Lets say for example I have a bunch of products in a table, and each product is owned by a client.
How do I write a query that gets the details of the cheapest product for each client.
And how do I expand that to say for example, get the cheapest product for each client and product type - for example if client 1's cheapest product is a washing machine, I dont want to show any more washing machines in the list.

I tried doing

select * from tblProducts group by fldClientID order by fldPrice limit 0, 10
That brought back only 1 result per client but the prices werent the cheapest
and when I tried

select * from tblProducts group by fldClientID, fldProductType order by fldPrice limit 0, 10

I got the same client coming up multiple times.

09-06-2006, 04:15 PM
select min(fldPrice) from tblProducts group by fldClientID

09-06-2006, 05:07 PM
that will only work if you are returning only the one field.

You will need something like this if you have more than one field you want to show:

from yourtable
where price =
(select min(p.price)
from yourtable as p
where product = p.product
and client = c.client)

09-06-2006, 09:19 PM
but that only brings back the details of the cheapest product - not cheapest per client

09-07-2006, 06:20 PM
This would return the cheapest for each client. It would return many records for a client if they have multiple products for the same price, that are both the cheapest.

select * from tblproducts t1 where t1.fldprice in (select min(fldprice) from tblproducts group by fldclientid)

09-08-2006, 01:41 AM
arnyinc, your query won't work, it will bring back a record that matches the minimum price even if it isn't the lowest for a client.

say sally has prices of 8, 15, 7 her lowest value is 7. if tom has values of 4, 5, 7 his lowest is 4 but the row with a price of 7 will also be returned from your query since it matches the list of lowest prices.

nancy, sorry my query was off the top of my head and not tested. try this:

from yourtable as a
inner join yourtable as b
on a.client = b.client
group by
having a.price = min(b.price)