Take a look at the CROSS APPLY operator in T-SQL. It is designed to do this.
Cheers, Kris On Sat, Jul 31, 2010 at 10:41 AM, Les Mizzell <[email protected]> wrote: > > Scott Brady wrote: >> There's probably a way to do it just in the query (using subqueries >> perhaps), but here's one option: > > > It's not that easy! > > > > > Change your query to this: > > Select catg, nltitle, nldate > > from newsletters > > order by catg, nldate DESC > > > > Then, for your output: > > <cfoutput query="yourQuery" group="catg"> > > #yourQuery.nldate# #yourQuery.catg# #yourQuery.nltitle#<br /> > > </cfoutput> > > > Your query returns the below (I've removed the grouping) > nldate catg nltitle > > -------------------------------------------- > > > 6/23/2010 bob another bob found! > > 6/23/2010 cars new nissan models > > 6/28/2010 grants something title > > 6/28/2010 grants another title > > 6/25/2010 grants more grant stuff > > 6/27/2010 news yes a title > > 6/27/2010 news good news today > > 6/23/2010 news tom just died > > 6/24/2010 toys new hotwheels > > > > Adding grouping give us: > > > 6/23/2010 bob another bob found! > > 6/23/2010 cars new nissan models > > 6/28/2010 grants something title > > 6/27/2010 news yes a title > > 6/24/2010 toys new hotwheels > > The order is now wrong - needs to be date newest to oldest. When you > sort by catg, nldate - the categories are date sorted, but not the full > list. What I need is: > > > 6/28/2010 grants something title > > 6/27/2010 news yes a title > > 6/24/2010 toys new hotwheels > > 6/23/2010 bob another bob found! > > 6/23/2010 cars new nissan models > > > See the problem? > > > __________ Information from ESET NOD32 Antivirus, version of virus signature > database 5328 (20100731) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335910 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
