Re: SQL selecting distinct items by date?
Sorry I am a little late in the reply on this one, but did you try using the MAX function. SELECT DISTINCT TOP 5 pivot.CategoryID, Category.Name, MAX(Item.DateAdded) FROM Category INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID INNER JOIN Item ON pivot.ItemID = Item.ItemID GROUP BY pivot.CategoryID, Category.Name ORDER BY MAX(Item.DateAdded) DESC ~| 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:342200 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL selecting distinct items by date?
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 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 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
RE: SQL selecting distinct items by date?
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 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
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 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