Output newest one item from each category

I'm trying to output a list of the most recent newsletters sorted by DATE. There are a number of categories that they fall under, and I must show only the newest 1 from each category.. Select catg, nltitle, nldate from newsletters order by date Might return this: nldate    catg    nltitle -------------------------------------------- 6/28/2010  grants    something title 6/28/2010  grants    another title   6/27/2010  news    yes a title 6/27/2010  news    good news today 6/25/2010  grants    more grant stuff 6/24/2010  toys    new hotwheels 6/23/2010  cars    new nissan models 6/23/2010  bob    another bob found! 6/23/2010  news    tom just died What I need to output is just: nldate    catg    nltitle -------------------------------------------- 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 "group by catg" won't work in the output, for obvious reasons. I've not been able to exactly get a query of queries to work either. Can someone point me in the correct direction here please? __________ Information from ESET NOD32 Antivirus, version of virus signature database 5326 (20100730) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com

There's probably a way to do it just in the query (using subqueries perhaps), but here's one option: 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> (that's untested, but that's the basic idea that should get you in the right direction) Scott ----- Excess quoted text cut - see Original Post for more ----- -- ----------------------------------------- Scott Brady http://www.scottbrady.net/

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

Take a look at the CROSS APPLY operator in T-SQL. It is designed to do this. Cheers, Kris ----- Excess quoted text cut - see Original Post for more -----

Kris Jones wrote: > Take a look at the CROSS APPLY operator in T-SQL. I don't have direct access to the database itself, so will need to do this in Coldfusion. Can't use "Cross Apply" there, right? This has got me totally befuddled at this point, to be honest... Gotta be some goof ball query of queries to pull this off, but I've spent almost two days on it now and nothing is working for me yet... __________ Information from ESET NOD32 Antivirus, version of virus signature database 5329 (20100731) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com

OK, the below *mostly* gives me the correct output in the correct order: <cfquery name="getIDLIST">   SELECT max(id) as id,  nl_hed   FROM nl_master   WHERE nl_status='pub'   group by nl_hed   order by nl_hed, id </cfquery> <cfset myLIST = #valuelist(getIDLIST.id)# /> <cfquery name="get_newsletters">   SELECT top 5 nl_hed, nl_date, nl_id, nl_title, goodURL   FROM nl_master   WHERE nl_status='pub'   AND id in (<cfqueryparam value="#myLIST#"               cfsqltype="CF_SQL_VARCHAR" list="yes">) ORDER by nl_date DESC </cfquery> But, that's sorta ugly and uses two queries. Any way to consolidate this down? There's a few cases where the max(id) might *not* be the newsletter with the highest date, but this probably won't crop up often enough to be noticed. So, it's a kludge that works, but still not the *real* solution. Ideally, I need to be able to find a way to:     Select distinct(nl_hed)     Plus select nl_date, nl_id, nl_title, goodURL     Order by nl_date DESC __________ Information from ESET NOD32 Antivirus, version of virus signature database 5329 (20100731) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com

Les, If that gives you mostly the right answer, then you can consolidate your two queries into one with a sub-select, instead of the valueList(): <cfquery name="get_newsletters">   SELECT TOP 5 nl_hed,     nl_date,     nl_id,     nl_title,     goodURL   FROM nl_master   WHERE nl_status = 'pub'     AND id IN (       SELECT MAX(id)       FROM nl_master       WHERE nl_status = 'pub'       GROUP BY nl_hed     )   ORDER by nl_date DESC </cfquery> > OK, the below *mostly* gives me the correct output in the correct > order: > > > <cfquery name="getIDLIST">    > SELECT max(id) as id,  nl_hed    > FROM nl_master    > WHERE nl_status='pub'    > group by nl_hed    ----- Excess quoted text cut - see Original Post for more -----    > SELECT top 5 nl_hed, nl_date, nl_id, nl_title, goodURL    > FROM nl_master    > WHERE nl_status='pub'    > AND id in (<cfqueryparam value="#myLIST#"                ----- Excess quoted text cut - see Original Post for more -----      > Select distinct(nl_hed)      > Plus select nl_date, nl_id, nl_title, goodURL      ----- Excess quoted text cut - see Original Post for more -----

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

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 <dennisp@uxbinternet.com>wrote: ----- Excess quoted text cut - see Original Post for more -----

Also, note that CROSS APPLY works just fine in ColdFusion.  Anything that works in SQL Server can be put between the CFQUERY tags.  ROW_NUMBER() and PARTITION will also work for you.  Try this: SELECT catg,   nltitle,   nldate FROM (     SELECT n.catg,       n.nltitle,       n.nldate,       ROW_NUMBER = ROW_NUMBER() OVER (         PARTITION BY n.catg         ORDER BY n.nldate DESC       )     FROM newsletters AS n   ) AS news WHERE news.ROW_NUMBER <= 1 ORDER BY news.ndldate DESC ----- Excess quoted text cut - see Original Post for more -----