SELECT articleTitle, articleDate, articleIssueID
FROM myArticles
ORDER BY articleIssueID DESC
</CFQUERY>
<cfoutput query="getarticle" group="articleissueID">
<<<<I think this might work for you.>>>
</cfoutput>
Terry
OR do a select max(articleissueID) query and then run a query for ONLY that issue.
----- Original Message -----
From: Ryan Sabir
To: CF-Talk
Sent: Sunday, March 28, 2004 3:24 PM
Subject: SQL grouped query help
Hey there folks, I gotta SQL question..
Imagine a database of news articles, each with an individual date, but
they are also grouped into issues, so a select might look like:
SELECT articleTitle, articleDate, articleIssueID
FROM myArticles
ORDER BY articleIssueID
Now, I want to select only the latest article from each issue in a
single query. Any ideas how I would do this?
The dodgy way would be to go:
<CFQUERY name="blah">
SELECT articleTitle, articleDate, articleIssueID
FROM myArticles
ORDER BY articleIssueID
</CFQUERY>
<CFLOOP query="blah">
<CFQUERY name="latestArticle">
SELECT TOP 1 articleTitle, articleDate, articleIssueID
FROM myArticles
WHERE articleIssueID = #articleIssueID#
ORDER BY articleDate DESC
</CFQUERY>
...
</CFLOOP>
But that means doing a query on every iteration of the loop.. I'm sure
theres a way to do this in one query using some sort of grouping, but
it escapes me...
thanks...
-----------------------
Ryan Sabir
Newgency Pty Ltd
2a Broughton St
Paddington 2021
Sydney, Australia
Ph (02) 9331 2133
Fax (02) 9331 5199
Mobile: 0411 512 454
http://www.newgency.com/index.cfm?referer=rysig
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
