find the least and the most

[asmsam]

I pull the below info from 4 databases and need to display just the employee(s) who have sold the most stoves in the least popular color. Any help would be great. Thanks all

Employee Quantity Sold Least Popular Color
---------------------- ------------- -------------------
Curt Robards 3 BLACK
Curt Robards 3 BLUE
Curt Robards 7 GOLD
Curt Robards 3 GREEN
Curt Robards 2 RED
Curt Robards 1 SILVER
Fred Bailey 2 BLACK
Fred Bailey 1 BLUE
Fred Bailey 4 GOLD
Fred Bailey 3 GRAY
Fred Bailey 4 GREEN
Fred Bailey 4 RED
Fred Bailey 9 SILVER
Tim Brown 2 BLACK
Tim Brown 4 BLUE
Tim Brown 2 GOLD
Tim Brown 2 GRAY
Tim Brown 1 RED
Tim Brown 4 SILVER

(19 row(s) affected)

 

[PHV]

Something like this ?
SELECT A.Employee, A.QuantitySold, A.LeastPopularColor
FROM theTable A INNER JOIN (
SELECT LeastPopularColor, MAX(QuantitySold) AS MaxQuantity FROM theTable GROUP BY LeastPopularColor
) M ON A.LeastPopularColor = M.LeastPopularColor AND A.QuantitySold = M.MaxQuantity

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[asmsam]

Thank for the help, I was not able to get it to work all the way because I get the Quantity from a count of the colors, and I dont know how sum a count.