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

SELECT TOP 1 ... and the rest of your query.

A.

On Wed, Jul 20, 2011 at 7:51 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> 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?
>