You want to display the latest docs before a selection in the drop down?
If so, why not just display the latest 5.
Assumign your'e using SQL server, try this.
<cfquery name="q_news" datasource="#REQUEST.dsn1#">
SELECT TOP 5 t_art_category.*, t_committees.*, t_articles.id_art,
t_articles.art_title, t_articles.art_category_ID,
t_articles.art_subcat_ID, t_articles.art_teaser,
t_articles.publication_month, t_articles.publication_year
FROM t_art_category, t_committees, t_articles
WHERE t_articles.art_category_ID = t_art_category.category_ID AND
t_committees.ID_comm = t_articles.art_subcat_ID
ORDER BY t_articles.publication_year DESC, t_articles.publication_month
DESC, cat_order
</cfquery>
-----Original Message-----
From: Mark Leder [mailto:[EMAIL PROTECTED]]
Sent: 19 August 2002 12:40
To: CF-Talk
Subject: Query Problem
Good Morning all,
I've set up a newsletter db which permits my client to enter in articles
and select the current month / year of publication from a select box.
There could be 5 - 6 articles posted for, say "August 2002". Since
those articles are the most recent, I want them to display in a
CFOUTPUT. So in September, when new articles get posted, the most
recent month becomes the current issue.
Problem is, I've tried using a MAX(dbfield) SQL query statement without
success. Can a MAX statement pull more than one database row (single
article) or am I trying to do a query the wrong way? What would be the
best way to write the statement? Here's what I have now:
<cfquery name="q_news" datasource="#REQUEST.dsn1#">
SELECT t_art_category.*, t_committees.*, t_articles.id_art,
t_articles.art_title, t_articles.art_category_ID,
t_articles.art_subcat_ID, t_articles.art_teaser,
t_articles.publication_month, t_articles.publication_year
FROM t_art_category, t_committees, t_articles
WHERE t_articles.art_category_ID = t_art_category.category_ID AND
t_committees.ID_comm = t_articles.art_subcat_ID AND
t_articles.publication_month =
MAX(t_articles.publication_month) AS newPubMonth AND
t_articles.publication_year =
MAX(t_articles.publication_year) AS newPubYear
ORDER BY cat_order
Thanks,
Mark
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
