If your using MS SQL 2005 or newer, you could use the row_number() feature-
one query using a funciton: with f(catg, nltitle, nldate, nth) as ( Select catg, nltitle, nldate, row_number() over (partition by catg order by catg, nldate desc) as nth from newsletters) select * from f where nth = 1 or create a view: Select catg, nltitle, nldate, row_number() over (partition by catg order by catg, nldate desc) as nth from newsletters select * from view where nth = 1 On Mon, Aug 2, 2010 at 2:12 PM, UXB Internet <[email protected]>wrote: > > I was actually thinking about this and like yourself have not found a way > to > do it in one query. I have a crude solution. This assumes you only have > the one table. In either case you need to get a unique list of the > categories then loop over it to create a dynamic query. > > <cfquery name="category"> > Select Distinct catg as 'thecategory' > From newsletters > Order by catg > </cfquery> > > > <cfquery> > <cfloop query="category"> > Select top 1 nldate, catg, nltitle > From newsletter > Where catg = '#thecategory#' > Order by nldate desc > UNION > </cfloop> > Select top 1 nldate, catg nltitle > From newsletter > Where 0=1 > > Order by {column number} > > </cfquery> > > It may be crude but this approach at least puts all your date into one > query > object. > > > > Dennis Powers > UXB Internet - A Website Design & Hosting Company > P.O. Box 6028 > Wolcott, CT 06716 > 203-879-2844 > http://www.uxbinternet.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:335928 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
