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
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
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,
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,
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#
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
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 lesm...@bellsouth.net wrote:
Scott Brady wrote:
There's probably a way to do it just in the query (using subqueries
perhaps), but here's one option:
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
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
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
10 matches
Mail list logo
