Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts

    Cheapest Price per Client

    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
    Code:
    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
    Code:
    select * from tblProducts group by fldClientID, fldProductType order by fldPrice  limit 0, 10
    I got the same client coming up multiple times.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Code:
    select min(fldPrice) from tblProducts group by fldClientID

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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:

    Code:
    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)

  • #4
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    but that only brings back the details of the cheapest product - not cheapest per client

  • #5
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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)

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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:

    Code:
    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)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •