asked on

Urgent please: how to select the max value in SQL Server 2005 or 2008

There is a purchase table in the data feed with contact identifiers that appear multiple times in the table based on products purchased. Write one SQL statement to meet the following criteria:
· Return one record per contact with the ProductCd and Quantity that is purchased most frequently, and Previous Year’s Revenue for that contact.
· If there are products with the same quantity purchased, any of the products can be returned

Table1: ProductsPurchased
ContactId ProductCd Quanity
001 A1 1
001 B1 3
001 D1 3
002 A1 3
002 C1 2
002 D1 5
004 A1 2
004 B1 5

Table2 Revenue
ContactId Previous Year’s Revenue
001 1200
002 2400
Temp.xls

Patrick Matthews

SELECT pp1.ContactID, pp1.ProductCd, pp1.Quantity, r.PrevYrRev
FROM ProductsPurchased pp1 INNER JOIN
    (SELECT pp2.ContactID, MAX(pp2.Quantity) AS MaxQuantity
    FROM ProductsPurchased pp2
    GROUP BY pp2.ContactID) x ON pp1.ContactID = x.ContactID AND 
        pp1.Quantity = x.MaxQuantity LEFT JOIN
    Revenue r ON x.ContactID = r.ContactID
ORDER BY pp1.ContactID

Open in new window

It brings back ties if >1 product tied for top quantity.

jc50967w

ASKER

Hi matthewspatrick, thank you for helping me here, but the return looks like this:

001      B1      3      1200
001      D1      3      1200
002      D1      5      2400
004      B1      5      NULL

jc50967w

ASKER

001 B1 3 1200

OR

001 D1 3 1200

should be the right output ?

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Patrick Matthews

Out of curiosity, why only return one product code in the case of ties?

If I bought 1 unit each of 100 different products, and nothing else, wouldn't it be misleading to show only product code for me with a quantity of 1?

jc50967w

ASKER

that's just my understanding of the requirment description, I might not be right.

Patrick Matthews

Anyway, http:#a38024737 gets you the ties, and http:#a38024815 brings back just one in the case of a tie. I think we're done here :)

jc50967w

ASKER

Yes, thank you very much.