> Hey there peoples, anyone else working the weekend shift? Not working the weekend shift - just trying not to move too much, stupid client-function-with-free-drinks last night :(((
> Now, I want to select only the latest article from each issue in a > single query. Any ideas how I would do this? OK, here's a warning: this may not be the best way to do this. But it does work (at least in my hungover state I'm pretty sure it does), and doesn't involve looping over a query. It assumes that you have a unique articleID field in your myArticle table... <cfquery name="groups"> SELECT MAX(articleID) AS topArticleID, articleIssueID FROM myArticles GROUP BY articleIssueID </cfquery> That gets you the top ID for each articleIssueID. Then you can get the records for all the articles that were identified in that query: <cfquery name="articles"> SELECT articleTitle, articleDate, articleIssueID FROM myArticles WHERE articleID IN (#ValueList(groups.topArticleID)#) ORDER BY articleDate </cfquery> The only problem I can see is if the highest ID is not the latest article in any issue. But you could probably work the top date into the aggregate function of the first query, maybe using a subquery. And bear in mind that someone else on the list will probably come up with a better method on Monday, probably in a single query :) Good luck, K. -- Kay Smoljak http://kay.smoljak.com --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
