RE: Select TOP X form all Groups
Hi Ian, Can you try this query: SELECT Type, Name, Views, (type + name) FROM dbo.Your_Table a WHERE type + name IN (SELECT TOP 2 type + Name FROM dbo.Your_Table b WHERE a.type = b.type ORDER BY views DESC) ORDER BY type, views DESC Mario -Original Message- From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 11:31 AM To: CF-Talk Subject: Re: Select TOP X form all Groups This is something to work from... I threw it together from the Northwind DB that ships with SQL Server SELECT C1.CategoryName AS CATEGORY, Count(C2.ProductID) AS 'TOTAL IN CATEGORY' FROM Categories C1, Products C2 WHERE C1.CategoryID = C2.CategoryID GROUP BY C1.CategoryName Mike - Original Message - From: "Jochem van Dieten" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, July 28, 2003 11:21 AM Subject: Re: Select TOP X form all Groups > Ian Skinner wrote: > > > 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. > > Something like this should work: > > SELECT a.type, a.name, a.views > FROM table a > WHERE ( > SELECT COUNT() > FROM table b > WHERE a.type = b.type > AND ( > b.views > a.views > OR ( > b.views = a.views > AND b.name < a.name > ) > ) > ) < 2 > > 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
Re: Select TOP X form all Groups
This is something to work from... I threw it together from the Northwind DB that ships with SQL Server SELECT C1.CategoryName AS CATEGORY, Count(C2.ProductID) AS 'TOTAL IN CATEGORY' FROM Categories C1, Products C2 WHERE C1.CategoryID = C2.CategoryID GROUP BY C1.CategoryName Mike - Original Message - From: "Jochem van Dieten" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, July 28, 2003 11:21 AM Subject: Re: Select TOP X form all Groups > Ian Skinner wrote: > > > 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. > > Something like this should work: > > SELECT a.type, a.name, a.views > FROM table a > WHERE ( > SELECT COUNT() > FROM table b > WHERE a.type = b.type > AND ( > b.views > a.views > OR ( > b.views = a.views > AND b.name < a.name > ) > ) > ) < 2 > > 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select TOP X form all Groups
Ian Skinner wrote: > 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. Something like this should work: SELECT a.type, a.name, a.views FROMtable a WHERE ( SELECT COUNT() FROMtable b WHERE a.type = b.type AND ( b.views > a.views OR ( b.views = a.views AND b.name < a.name ) ) ) < 2 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Select TOP X form all Groups
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: TYPENAMEVIEWS Campground X 4 Campground Bar 3 Trail Here3 Trail There 3 LakeMed 3 LakeSmall 2 >From Table: TYPENAMEVIEWS Campground Foo 2 Campground Bar 3 Campground X 4 Campground Y 3 Trail Joe's 1 Trail Pac 2 Trail Here3 Trail There 3 LakeBig 1 LakeSmall 2 LakeMed 3 For example: NAMEVIEWS rec14 rec23 rec33 rec43 In this case, I would want rec1 and any one of the following 3. Another example: NAMEVIEWS rec15 rec25 rec35 rec43 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: NAMEVIEWS rec14 rec23 rec33 rec43 In this case, I would want rec1 and any one of the following 3. Another example: NAMEVIEWS rec15 rec25 rec35 rec43 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 NAMEVIEWS > CampgroundX 4 > CampgroundBar 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
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: NAMEVIEWS rec14 rec23 rec33 rec43 In this case, I would want rec1 and any one of the following 3. Another example: NAMEVIEWS rec15 rec25 rec35 rec43 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 NAMEVIEWS > CampgroundX 4 > CampgroundBar 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
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: NAMEVIEWS rec14 rec23 rec33 rec43 In this case, I would want rec1 and any one of the following 3. Another example: NAMEVIEWS rec15 rec25 rec35 rec43 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 NAMEVIEWS > CampgroundX 4 > CampgroundBar 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 NAMEVIEWS > CampgroundX 4 > CampgroundBar 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
