Here's what I ended up with SELECT distinct TOP 5 pc.nCategoryID, c.sCategory, YEAR(p.dPartDate) AS theyear, MONTH(p.dPartDate) AS themonth, DAY(p.dPartDate) AS theday FROM Category c INNER JOIN PartCat pc ON pc.nCategoryID=c.nCategoryID inner join part p on pc.npartid=p.npartid order by theyear DESC, themonth desc, theday desc
Adding any part-specific fields to the select list results in duplicate CategoryIDs, and I don't fully understand why the DISTINCT can't remain exclusive to the CategoryID field, but so far I think it'll do what's needed. The results can be looped over to get more detail which means subqueries, but it functions. Combining TOP and DISTINCT with GROUP/ORDER remains somewhat uncertain... -----Original Message----- From: John M Bliss [mailto:bliss.j...@gmail.com] Sent: Sunday, February 13, 2011 10:55 AM To: cf-talk Subject: Re: SQL selecting distinct items by date? SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, YEAR(Item.DateAdded) AS theyear, MONTH(Item.DateAdded) AS themonth, DAY(Item.DateAdded) AS theday On Sun, Feb 13, 2011 at 12:51 PM, wabba <must...@wabba.net> wrote: > > More on SQL-topic than CF (MS SQL), but hopefully is an easy one. I have > items with a datetime field that stores when the items are added to the DB. > There is a pivot table that links items to categories. I'm trying to pull > out the top 5 unique categories with the newest-added items. This is what > I'm "trying" to do even though the syntax doesn't work: > > SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, Item.DateAdded > FROM Category > INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID > INNER JOIN Item ON pivot.ItemID = Item.ItemID > ORDER BY Item.DateAdded DESC > > I can get close, but the DateAdded fields are always unique (sometimes only > seconds apart, but unique) so no matter what I do it always thinks the > result records are unique and won't give me unique CategoryIDs. Ideas? > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342181 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
