Member Avatar for 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

Edited by Ezzaral because: Added code tags. Please use them to format any code that you post.
  • 2 Contributors
  • 3 Replies
  • 99 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

Nice follow-up from and good pointer from . For “top N per month” there are two practical patterns to pick from depending on ties and scale.

One simple, readable approach is a per-group TOP using CROSS APPLY — it returns exactly N rows per month and is easy to tune with an ORDER BY tie-breaker so results are deterministic:

-- per-month top 3 (exactly 3 rows per month)
SELECT m.CorrectionMth, t.OrigDesc, t.NewDesc, t.Total
FROM (
  SELECT DISTINCT CorrectionMth FROM YourTable
) m
CROSS APPLY (
  SELECT TOP (3) OrigDesc, NewDesc, Total
  FROM YourTable x
  WHERE x.CorrectionMth = m.CorrectionMth
  ORDER BY Total DESC, OrigDesc  -- add deterministic tie-breaker
) t
ORDER BY m.CorrectionMth, t.Total DESC;

If ties should be preserved (e.g., many rows share the 3rd-place total), use ranking functions like RANK() or DENSE_RANK() and filter rk <= 3. RANK() will include all tied rows for a rank; ROW_NUMBER() would force an arbitrary single ordering when totals tie.

Performance tips: for large tables add a nonclustered index on the grouping + ordering columns and INCLUDE the payload columns, for example:

CREATE NONCLUSTERED INDEX IX_YourTable_Mth_Total
  ON dbo.YourTable (CorrectionMth, Total)
  INCLUDE (OrigDesc, NewDesc);

Also, compute or persist a month-only value (a first-of-month date or small int year*100+month) to avoid repeated date math in filters and to allow index seeks.

Cautions: always specify a stable ORDER BY (add a secondary column or primary key) so ties don’t produce non-repeatable results. For large numbers of groups test both approaches — window functions do a single scan + sort, CROSS APPLY can be faster when an index supports the per-month lookup.

Microsoft docs on window functions and APPLY are useful references:

Recommended Answers

All 3 Replies

Member Avatar for 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

Member Avatar for adam_k

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.