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:342179 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
