Lynn Emery

Lynn Emery

  • NA
  • 47
  • 40k

Select Where Maximum

Mar 6 2013 6:24 AM
Hi I am trying to create an SQL view in Visual studio.
I want to filter out results to show the maximum sales for each customer based on each month and year and to display a description of the sales
I have the following statement:

SELECT        Customer, Month, Year, sales, Description
FROM            dbo.salestable
GROUP BY Customer, Month, Year, sales, Description
HAVING        (sales =
                             (SELECT        MAX(dbo.salestable.ppm) AS Expr1))

This doesn't seem to be working as I get duplicates for each month rather than one entry for each customer per month which should be the maximum sales for that customer.
I think the group by is causing an issue but it forces me to group each field.

Hopefully there is a simple solution?
Thank you for your help in advance.

Answers (1)