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