Yes, I tried the TOP X command, but in my original post, I mentioned that I need the TOP X rows for each of a group a categories. See below. If there are more then two results that match as top of the category, I don't care which two I get, just so I get only the TOP X for each category.
Desired Results: TYPE NAME VIEWS Campground X 4 Campground Bar 3 Trail Here 3 Trail There 3 Lake Med 3 Lake Small 2 >From Table: TYPE NAME VIEWS Campground Foo 2 Campground Bar 3 Campground X 4 Campground Y 3 Trail Joe's 1 Trail Pac 2 Trail Here 3 Trail There 3 Lake Big 1 Lake Small 2 Lake Med 3 For example: NAME VIEWS rec1 4 rec2 3 rec3 3 rec4 3 In this case, I would want rec1 and any one of the following 3. Another example: NAME VIEWS rec1 5 rec2 5 rec3 5 rec4 3 In this case, I would want any two of the first three records, either random, alphabetically, or unordered would be fine. -------------- Ian Skinner Web Programmer BloodSource Sacramento, CA -----Original Message----- From: Ciliotta, Mario [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:54 AM To: CF-Talk Subject: RE: Select TOP X form all Groups Ian, Have you tried using TOP in your query. I have used it with SQLServer7.0 Here is an example form the SQLServer Online Books: Limiting Result Sets Using TOP and PERCENT The TOP clause limits the number of rows returned in the result set. TOP n [PERCENT] n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return: TOP 120 /*Return the top 120 rows of the result set. */ TOP 15 PERCENT /* Return the top 15% of the result set. */. If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned. The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways: The SET ROWCOUNT limit applies to building the rows in the result set before an ORDER BY is evaluated. Even if ORDER BY is specified, the SELECT statement is terminated when n rows have been selected. n rows are selected, then ordered and returned to the client. The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off. -Mario -----Original Message----- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:03 AM To: CF-Talk Subject: RE: Select TOP X form all Groups I need the top X records, if more then X records could be considered the top, I just need X records based on any other order of convenience. For example: NAME VIEWS rec1 4 rec2 3 rec3 3 rec4 3 In this case, I would want rec1 and any one of the following 3. Another example: NAME VIEWS rec1 5 rec2 5 rec3 5 rec4 3 In this case, I would want any two of the first three records, either random, alphabetically, or unordered would be fine. Thank You -------------- Ian Skinner Web Programmer BloodSource Sacramento, CA -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 3:51 AM To: CF-Talk Subject: Re: Select TOP X form all Groups Ian Skinner wrote: > I have a table something like the below. I would like to create a select > that will retrieve the TOP 2 records for each category? I can't seem to get > my head around this one, is it really that difficult? > > Desired Results: > TYPE NAME VIEWS > Campground X 4 > Campground Bar 3 So which results do you want if there are 3 names with the same number of views? Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
