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

Could you :
 
select top 1 pr.* from 
(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) pr
where pr.LFRank = 1
 
Mark A. Matte



 

> Date: Wed, 20 Jul 2011 07:51:31 -0400
> From: jwcolby at colbyconsulting.com
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] How to get top1 of group by
> 
> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>