jwcolby
jwcolby at colbyconsulting.com
Wed Jul 20 07:07:49 CDT 2011
No, that gets the top 1 of all the records. The query is returning 1 to N records for every product. I need 1 record for every product. John W. Colby www.ColbyConsulting.com On 7/20/2011 7:57 AM, Arthur Fuller wrote: > 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? >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >