Get the top 3 based on correction month

[jovillanuev]

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table 
GO
Create Table #Table
(
  CorrectionDate datetime,
  Origdesc nvarchar(50),
  Newdesc nvarchar(50),
  Total int
)

Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','RMAWAR','NTF',193)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','NTF','RMAWAR',108)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','RMAWAR','Sub Abuse',86)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','NTF','Other',71)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','Other','Out of Warranty',129)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','RMAWAR','Sub Abuse',	103)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','Cosmetic','NTF',91)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','Scrap','Out of Warranty',35)

Derived Result:

Correctiondate|OrigDesc|NewDesc|Total
----------------------------------------------------
2011-08-01 00:00:00.000 |RMAWAR |NTF | 193
2011-08-01 00:00:00.000 |NTF |RMAWAR | 108
2011-08-01 00:00:00.000 |RMAWAR |Sub Abuse |86
2011-09-01 00:00:00.000 |Other |Out of Warranty| 129
2011-09-01 00:00:00.000 |RMAWAR |Sub Abuse| 103
2011-09-01 00:00:00.000 |Cosmetic|NTF |91

Thanks,

JOV

[adam_k]

This article might give you a starting point: http://www.daniweb.com/web-development/databases/ms-sql/threads/380157

[jovillanuev]

Thank you very much Adam_K.

BTw, here is the query.

Select
tmp.Origdesc,
tmp.Newdesc,
tmp.CorrectionMth,
tmp.Total
From (Select ROW_NUMBER() over (partition by correctionMth order by total desc) as rowno,
Origdesc,
Newdesc,
CorrectionMth,
Total
From #VpData2 ) tmp
where tmp.rowno <=1

[adam_k]

Please be kind enough to mark this thread as solved, if your problem has been solved.
Thanks