RE: selecting top n records - RESOLVED
Mark, Your going to need to ORDER that recordset by COUNT(ID_PRODUCT), because it will select the TOP 3 of the recordset, not necessarily the Largest values, the ordering it uses is based on the indexes it has setup, you should never assume ordering, unless you use ORDER BY. The fact that it is working as you expect right now must be just a coincidence. Also I noticed that you are using a session variable to store the datasource name... do you have a datasource for each user? If not you should consider a different scope. Pete Freitag ([EMAIL PROTECTED]) CFDEV.COM ColdFusion Developers Resources http://www.cfdev.com/ -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 4:56 PM To: CF-Talk Subject: FW: selecting top n records - RESOLVED Here's the answer to the question for anyone who is curious. SELECT TOP 3 COUNT(ID_PRODUCT) as thecount, ID_PRODUCT FROM ORDER_LINEITEMS GROUP BY ID_PRODUCT = Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 1:07 PM To: cf-talk Subject: selecting top n records I need to select the top selling products from a table of lineitems. Top selling products would be those whose ID_PRODUCT shows up the most in the ORDERS_LINEITEMS table. Any clues from the SQL gurus out there? ---mark = Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: selecting top n records
SELECT * FROm Table LIMIT 10; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 3:23 PM To: CF-Talk Subject: RE: selecting top n records First thought would be something like: SELECT TOP 10 COUNT(PRODUCTID) AS COUNTER, PRODUCTID FROMORDERDETAIL GROUP BYPRODUCTID ORDER BYCOUNTER DESC There may be something faster, but this works... - BILL - -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 2:07 PM To: CF-Talk Subject: selecting top n records I need to select the top selling products from a table of lineitems. Top selling products would be those whose ID_PRODUCT shows up the most in the ORDERS_LINEITEMS table. Any clues from the SQL gurus out there? ---mark = Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: selecting top n records
First thought would be something like: SELECT TOP 10 COUNT(PRODUCTID) AS COUNTER, PRODUCTID FROMORDERDETAIL GROUP BYPRODUCTID ORDER BYCOUNTER DESC There may be something faster, but this works... - BILL - -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 2:07 PM To: CF-Talk Subject: selecting top n records I need to select the top selling products from a table of lineitems. Top selling products would be those whose ID_PRODUCT shows up the most in the ORDERS_LINEITEMS table. Any clues from the SQL gurus out there? ---mark = Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: selecting top n records
assuming that productId is a primary key your query will always have a count of one I think the table should not be Products but ORDERS_LINEITEMS - Original Message - From: "Kevin Mansel" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, July 31, 2001 2:13 PM Subject: RE: selecting top n records > Try this... > > > maxrows="n"> > SELECT COUNT(ProductID) AS TopN, ProductID > FROM Products > GROUP BY ProductID > ORDER BY TopN DESC > > > > remember to put something of numerical value in the maxrows attribute. > > > hth > > kev > > ~ > Kevin Mansel > Senior Web Developer > Fox Communications > [EMAIL PROTECTED] > DL : 425-649-1321 > C : 425-346-7221 > > > > -Original Message- > From: Mark Warrick [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 31, 2001 1:07 PM > To: CF-Talk > Subject: selecting top n records > > > I need to select the top selling products from a table of lineitems. Top > selling products would be those whose ID_PRODUCT shows up the most in the > ORDERS_LINEITEMS table. > > Any clues from the SQL gurus out there? > > ---mark > > = > Mark Warrick - Fusioneers.com > Personal Email: [EMAIL PROTECTED] > Business Email: [EMAIL PROTECTED] > Phone: 714-547-5386 > Efax: 801-730-7289 > Personal URL: http://www.warrick.net > Business URL: http://www.fusioneers.com > ICQ: 125160 / AIM: markwarric > = > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: selecting top n records
SELECT TOP 10 COUNT(ID_PRODUCT) AS Sold, ID_PRODUCT FROM ORDERS_LINEITEMS GROUP BY ID_PRODUCT ORDER BY COUNT(ID_PRODUCT) DESC you can replace the 10 with N Pete Freitag ([EMAIL PROTECTED]) CFDEV.COM ColdFusion Developers Resources http://www.cfdev.com/ -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 4:07 PM To: CF-Talk Subject: selecting top n records I need to select the top selling products from a table of lineitems. Top selling products would be those whose ID_PRODUCT shows up the most in the ORDERS_LINEITEMS table. Any clues from the SQL gurus out there? ---mark = Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: selecting top n records
Try this... SELECT COUNT(ProductID) AS TopN, ProductID FROM Products GROUP BY ProductID ORDER BY TopN DESC remember to put something of numerical value in the maxrows attribute. hth kev ~ Kevin Mansel Senior Web Developer Fox Communications [EMAIL PROTECTED] DL : 425-649-1321 C : 425-346-7221 -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 1:07 PM To: CF-Talk Subject: selecting top n records I need to select the top selling products from a table of lineitems. Top selling products would be those whose ID_PRODUCT shows up the most in the ORDERS_LINEITEMS table. Any clues from the SQL gurus out there? ---mark = Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
