[dba-SQLServer] How to get top1 of group by

jwcolby jwcolby at colbyconsulting.com
Wed Jul 20 06:51:31 CDT 2011


The following sql gets me the topN of a group where based on rank.

with PR as
(
SELECT     Product, PartNo, Buildable, LimitingFactor, rank() over (partition by PRFW.product order 
by PRFW.LimitingFactor) as LFRank
FROM         dbo.VpARTS_REQUIREMENTS_FOR_WORKORDERS_OWBreakout as PRFW
)

select * from PR WHERE PR.LFRank = 1

but if there are several of the same rank it returns all of the records with the top rank.  I need 
to get only 1 item per product.

Any suggestions?
-- 
John W. Colby
www.ColbyConsulting.com


More information about the dba-SQLServer mailing list