My gut instinct was "this should be easy" too. But, obviously I didn't test
it.
If the article id's are incremented in a parrallel relationship with date
(ie, the higher the articleid the "newer" the article), though, there might
be an easy solution. (That's a big "if," though.)
<cfquery...>
SELECT articleTitle, articleDate, articleIssueID
FROM myArticles
WHERE articleID IN (SELECT max(articleid) FROM myArticles GROUP BY
articleIssueID)
</cfquery>
Or, here's another alternative. It's a bit kludgey, but so is the initial
query (because if two articles have the same date, isn't "top" arbitrarily
determined?). This one (tested in Oracle 8.1.7), returns one "newest"
article per issue. "Newest" means that the date added was the latest date.
For those articles with matching dates, then the highest articleid is
returned. (The internal order by is superfluous, but made my testing
easier.)
SELECT t.articleid, t.articletitle, t.articledate, t.issueid
FROM myarticles t, (SELECT a.issueid, MAX(a.articleid) as articleid
FROM myarticles a, (select max(articledate) as
articledate, issueid
FROM
myarticles
GROUP BY
issueid) b
WHERE a.articledate = b.articledate
AND a.issueid = b.issueid
GROUP BY a.issueid
ORDER BY a.issueid) s
WHERE t.articleid = s.articleid
ORDER BY t.issueid, t.articletitle
----- Original Message -----
From: "Pascal Peters"
> Won't work. You can't have a column in the order by that is not in the
> group by and TOP 1 only returns the first record, not the first record
> for each articlIssueID. I started by replying to this mail this morning
> thinking "this should be easy", but I didn't find a solution. I
> discussed it with my colleagues and they had no solution either. Maybe
> some SQL guru can find one, but nobody in my company did. The only
> solution I could come up with was: write a stored procedure.
>
> Pascal
>
> > -----Original Message-----
> > From: Deanna Schneider [mailto:[EMAIL PROTECTED]
> > Sent: maandag 29 maart 2004 15:39
> > To: CF-Talk
> > Subject: Re: SQL grouped query help
> >
> > ----- Original Message -----
> > 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>
> >
> >
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
