You could add the fields you need to your query then do a GROUP on the CFOUTPUT. That will then be able to weed through the duplicates caused by differences in the data. Also could/should eliminate the need to add queries within your loop.
On Sun, Feb 13, 2011 at 1:21 PM, wabba <must...@wabba.net> wrote: > > 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:342193 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
