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
> 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
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:335931
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
