Select Where Maximum

Question

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.

Jignesh Trivedi · Answer

hi i think there is problem with sales include in Group by statement. please refer following TSQL it might help you Create Table #temp ( Customer varchar(50) Month varchar(5) Year varchar(4) sales int Description varchar(50) ) Insert into #temp values('10000''Jan''2013'2000'test') Insert into #temp values('10000''Jan''2013'5000'test') Insert into #temp values('10000''Feb''2013'3000'test') Insert into #temp values('20000''Jan''2013'3000'test') Insert into #temp values('20000''Feb''2013'3000'test') Insert into #temp values('20000''Jan''2013'4000'test') Insert into #temp values('30000''Jan''2013'5000'test') Insert into #temp values('30000''Feb''2013'6000'test') Insert into #temp values('30000''March''2013'7000'test') Insert into #temp values('30000''Jan''2013'3000'test') Insert into #temp values('30000''Feb''2013'3000'test') select * from #temp SELECT Customer Month Year max(sales) as Sales Description FROM #temp GROUP BY Customer Month Year Description hope this will help you