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... SELECTarticleTitle, articleDate, articleIssueID FROMmyArticles 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 BYarticleIssueID /CFQUERY CFLOOP query=blah CFQUERY name=latestArticle SELECT TOP 1 articleTitle, articleDate, articleIssueID FROM myArticles WHERE articleIssueID = #articleIssueID# ORDER BYarticleDate 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]
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: cfquery... SELECTarticleTitle, articleDate, articleIssueID FROMmyArticles 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]
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) SELECTarticleIssueId, 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
CFQUERY name=blah SELECT articleTitle, articleDate, articleIssueID FROM myArticles ORDER BYarticleIssueID 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 BYarticleIssueID 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 BYarticleIssueID /CFQUERY CFLOOP query=blah CFQUERY name=latestArticle SELECT TOP 1 articleTitle, articleDate, articleIssueID FROM myArticles WHERE articleIssueID = #articleIssueID# ORDER BYarticleDate 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]
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.) cfquery... SELECTarticleTitle, articleDate, articleIssueID FROMmyArticles 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, (SELECTa.issueid, MAX(a.articleid) as articleid FROM myarticles a, (select max(articledate) as articledate, issueid FROM myarticles GROUP BY issueid) b WHEREa.articledate = b.articledate ANDa.issueid = b.issueid GROUP BY a.issueid ORDER BY a.issueid) s WHEREt.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... SELECTarticleTitle, articleDate, articleIssueID FROMmyArticles 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]
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 CFQUERY name=blah SELECT articleTitle, articleDate, articleIssueID FROM myArticles ORDER BYarticleIssueID 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. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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) SELECTarticleIssueId, 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]
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 BYarticleIssueID 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 BYarticleIssueID /CFQUERY CFLOOP query=blah CFQUERY name=latestArticle SELECT TOP 1 articleTitle, articleDate, articleIssueID FROM myArticles WHERE articleIssueID = #articleIssueID# ORDER BYarticleDate 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]
