Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Using max with four columns

    I'm stuck with a MAX query, where I need to return 4 columns form a five columned table.

    The table Purchases consists of columns:

    Customer ID (long int)
    WareGroupNumber (a long int ID)
    SubItenmGriup (tinyint)
    Subitem (tiny)
    Value (currency)

    where the combo of waregroupnumber+subitemgroup+subitem is a unique key), the custoemr ID relates to whatever customer purchased it (foreign key), and the value is the price of the unique product purchased, BUT, a unique product doesn't have a set price, it varies from customer to customer.

    What I need to get hold of, is the single most expensive product purchased by each unique customer, what the product was worth and its unique ID.

    Code:
    Select Customer, WaregroupNumber, SubitemGroup, Subitem, Max(value)
    FROM Purchases
    GROUP BY Customer, WaregroupNumber, SubitemGroup, Subitem
    The above simply returns all the products the customer purchased and its max value, not the single most expensive product purchased.

    Code:
    Select distinct Customer, Max(value)
    FROM Purchases
    GROUP BY Customer
    Gets close to the target, but I need the product they purchased as well.

    How to?

    Thanks for any help in advance,

    Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    Select p.Customer
         , p.WaregroupNumber
         , p.SubitemGroup
         , p.Subitem
         , p.Value
      FROM Purchases AS p
    INNER
      JOIN ( SELECT Customer
                  , MAX(value) AS maxvalue
               FROM Purchases
             GROUP 
                 BY Customer ) AS m
        ON m.Customer = p.Customer
       AND m.maxvalue = p.Value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Brilliant.. inlining.

    Thanks a bunch!

    Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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