From: "Ryan Sabir"
> Now, I want to select only the latest article from each issue in a
> single query. Any ideas how I would do this?
Try something like this:
<cfquery...>
SELECT articleTitle, articleDate, articleIssueID
FROM myArticles
WHERE articleID IN (SELECT TOP 1 articleid FROM myArticles GROUP BY
articleIssueID ORDER BY articledate DESC )
</cfquery>
> <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]
