Remove the 'top 5' and add a 'where' clause... WHERE t_articles.publication_year = #year(now())# AND t_articles.publication_month = #month(now())#
OR in SQl server you could try.. WHERE t_articles.publication_year = year(Getdate()) AND t_articles.publication_month = month(getdate()) That will give you all articles from the current month. Altough it's highly likley that early in the month you'll get 0 records. I'd store and actual publication date to be honest, rather than 2 separate fields (year/month). -----Original Message----- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: 19 August 2002 13:22 To: CF-Talk Subject: RE: Query Problem The only issue here is that the amount of articles could vary from month to month. Thanks, Mark -----Original Message----- From: Craig Dudley [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 7:56 AM To: CF-Talk Subject: RE: Query Problem 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
