Now where did I put my BOL?! :OS -----Original Message----- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: 29 September 2005 13:38 To: CF-Talk Subject: RE: Now more query problems... boo hoo
You can use it in the order by clause, he just needs to group all the columns in his query with GROUP BY. Taco Fleur - Pacific Fox an industry leader with commercial IT experience since 1994 . http://www.pacificfox.com - Web Design and Development > -----Original Message----- > From: Adrian Lynch [mailto:[EMAIL PROTECTED] > Sent: Thursday, 29 September 2005 10:30 PM > To: CF-Talk > Subject: RE: Now more query problems... boo hoo > > > You can't use COUNT in the ORDER BY clause. (Please someone > back me up here else I'm off to spend some time with Book Online!). > > Ade > > -----Original Message----- > From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] > Sent: 29 September 2005 13:20 > To: CF-Talk > Subject: Now more query problems... boo hoo > > > Jumping from one query problem to another... just when it was > safe to to run a query another problem leapt out of the dungeons. > > I would like to only pull the top 8 authors who have the most > articles (articleID) > > I have specified only to get the 8 maxrows but the problem > i'm having is when i try to add to the ORDER BY COUNT(articleID) DESC > > My error is: > > [Macromedia][SQLServer JDBC Driver][SQLServer]Column > 'tbl_020authorDetails.authorID' is invalid in the select list > because it is not contained in an aggregate function and > there is no GROUP BY clause. > > It can be seen at: http://www.020.com/testsql.cfm > > > <cfquery name="GetAuthors" datasource="user020" maxrows="8"> > SELECT > tbl_020authorDetails.authorID, > tbl_020authorDetails.authorFirstName, > tbl_020authorDetails.authorSurname, > tbl_020authorDetails.authorPhotograph, > tbl_020authorDetails.authorAboutMe, > tbl_020articleDetails.articleID, > tbl_020articleDetails.articleTitle > FROM tbl_020articleDetails > INNER JOIN tbl_020authorDetails ON > tbl_020articleDetails.authorID = tbl_020authorDetails.authorID > WHERE tbl_020authorDetails.authorPhotograph <> '' AND > tbl_020articleDetails.articleSubTypeID <> 55 > ORDER BY tbl_020authorDetails.authorID, COUNT(articleID) > DESC </cfquery> > > <cfoutput query="GetAuthors" group="authorID"> > <p><strong>#authorFirstName#</strong></p> > > <p>my articles:</p> > <cfoutput>#articleTitle#<br /></cfoutput> > </cfoutput> > > Any help would be great. > > Thanks, > > Stuart > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219590 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
