RE: SQL grouped query help
You know... I love this idea - very cool I had not thought of that. -Mark Mark A. Kruger, MCSE, CFG www.cfwebtools.com www.necfug.com http://blog.mxconsulting.com ...what the web can be! -Original Message- From: Nick de Voil [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 8:25 AM To: CF-Talk Subject: Re: SQL grouped query help > > > Now, I want to select only the latest article from each issue in a > > > single query. Any ideas how I would do this? Kind of sneaky, but something like this will do it (substitute the relevant type conversion & string concatenation functions for your DBMS) SELECT articleIssueId, articleTitle, articleDate FROMmyArticles WHERE convert(char(16), articleIssueID) + '_' + convert(char(16), articleID) IN (SELECT convert(char(16), articleIssueID) + '_' + convert(char(16), MAX(articleid)) FROM myArticles GROUP BY articleIssueID) Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL grouped query help
This is also what we came up with, but it means that if you have 10 issues and 1000 articles, you have to retreive 1000 records to show 10 articles. The Order by clause would have to be WHERE articleIssueID, articleDate DESC I can't see how the second solution would help. Pascal > -Original Message- > From: Terry Troxel [mailto:[EMAIL PROTECTED] > Sent: maandag 29 maart 2004 16:37 > To: CF-Talk > Subject: Re: SQL grouped query help > > > SELECT articleTitle, articleDate, articleIssueID > FROM myArticles > ORDER BY articleIssueID DESC > > > <<<>> > > Terry > > OR do a select max(articleissueID) query and then run a query > for ONLY that issue. > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL grouped query help
Hm...I was guessing, since I don't have SQL server/not familiar with "top." 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.) SELECT articleTitle, articleDate, articleIssueID FROMmyArticles WHERE articleID IN (SELECT max(articleid) FROM myArticles GROUP BY articleIssueID) 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: > > > > SELECT articleTitle, articleDate, articleIssueID > > FROMmyArticles > > WHERE articleID IN (SELECT TOP 1 articleid FROM myArticles GROUP BY > > articleIssueID ORDER BY articledate DESC ) > > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL grouped query help
SELECT articleTitle, articleDate, articleIssueID FROM myArticles ORDER BY articleIssueID DESC <<<>> 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: SELECT articleTitle, articleDate, articleIssueID FROM myArticles ORDER BY articleIssueID SELECT TOP 1 articleTitle, articleDate, articleIssueID FROM myArticles WHERE articleIssueID = #articleIssueID# ORDER BY articleDate DESC ... 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]
Re: SQL grouped query help
> > > Now, I want to select only the latest article from each issue in a > > > single query. Any ideas how I would do this? Kind of sneaky, but something like this will do it (substitute the relevant type conversion & string concatenation functions for your DBMS) SELECT articleIssueId, articleTitle, articleDate FROMmyArticles WHERE convert(char(16), articleIssueID) + '_' + convert(char(16), articleID) IN (SELECT convert(char(16), articleIssueID) + '_' + convert(char(16), MAX(articleid)) FROM myArticles GROUP BY articleIssueID) Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL grouped query help
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: > > SELECT articleTitle, articleDate, articleIssueID > FROMmyArticles > WHERE articleID IN (SELECT TOP 1 articleid FROM myArticles GROUP BY > articleIssueID ORDER BY articledate DESC ) > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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: SELECT articleTitle, articleDate, articleIssueID FROMmyArticles WHERE articleID IN (SELECT TOP 1 articleid FROM myArticles GROUP BY articleIssueID ORDER BY articledate DESC ) > > SELECT articleTitle, articleDate, articleIssueID > FROM myArticles > ORDER BY articleIssueID > > > > > SELECT TOP 1 articleTitle, articleDate, articleIssueID > FROM myArticles > WHERE articleIssueID = #articleIssueID# > ORDER BY articleDate DESC > > > ... > > > > 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]
