...

View Full Version : Cheapest Price per Client



NancyJ
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.

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

guelphdad
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:



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

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

arnyinc
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)

guelphdad
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:



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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum