RE: Selecting TOP N
Which way round are you putting it in? This works: SELECT DISTINCT TOP 10 fields -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 8:00 AM To: CF-Talk Subject: Selecting TOP N I can't seem to get SELECT TOP 10 to work when the query below has a DISTINCT clause in it... Can anyone shed some light into my confusion. SELECT DISTINCT(T.opportunityId), O.title, O.createDate, U.firstName, U.lastName, U.emailAddress FROM tbl_ta T INNER JOIN tbl_opportunity O ON (T.opportunityId = O.opportunityId) INNER JOIN tbl_user U ON (O.createUserId = U.userId) WHERE EXISTS ( SELECT O.title FROM tbl_opportunity O ) ORDER BY O.title ASC Thanks! Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Selecting TOP N
Thanks! I just had it backwards Mike -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 8:10 AM To: CF-Talk Subject: RE: Selecting TOP N Which way round are you putting it in? This works: SELECT DISTINCT TOP 10 fields -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 8:00 AM To: CF-Talk Subject: Selecting TOP N I can't seem to get SELECT TOP 10 to work when the query below has a DISTINCT clause in it... Can anyone shed some light into my confusion. SELECT DISTINCT(T.opportunityId), O.title, O.createDate, U.firstName, U.lastName, U.emailAddress FROM tbl_ta T INNER JOIN tbl_opportunity O ON (T.opportunityId = O.opportunityId) INNER JOIN tbl_user U ON (O.createUserId = U.userId) WHERE EXISTS ( SELECT O.title FROM tbl_opportunity O ) ORDER BY O.title ASC Thanks! Mike _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Selecting TOP N
If MySQL SELECT yourfieds FROM yourtable LIMIT 10 Cheers - Original Message - From: Philip Arnold [EMAIL PROTECTED] Date: Fri, 9 Jan 2004 08:09:43 -0500 To: CF-Talk [EMAIL PROTECTED] Subject: RE: Selecting TOP N Which way round are you putting it in? This works: SELECT DISTINCT TOP 10 fields -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 8:00 AM To: CF-Talk Subject: Selecting TOP N I can't seem to get SELECT TOP 10 to work when the query below has a DISTINCT clause in it... Can anyone shed some light into my confusion. SELECT DISTINCT(T.opportunityId), O.title, O.createDate, U.firstName, U.lastName, U.emailAddress FROM tbl_ta T INNER JOIN tbl_opportunity O ON (T.opportunityId = O.opportunityId) INNER JOIN tbl_user U ON (O.createUserId = U.userId) WHERE EXISTS ( SELECT O.title FROM tbl_opportunity O ) ORDER BY O.title ASC Thanks! Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: selecting top n records
Try this... cfquery name=topnrecords datasource=dsn username=user password=pass maxrows=n SELECT COUNT(ProductID) AS TopN, ProductID FROM Products GROUP BY ProductID ORDER BY TopN DESC /cfquery 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
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... cfquery name=topnrecords datasource=dsn username=user password=pass maxrows=n SELECT COUNT(ProductID) AS TopN, ProductID FROM Products GROUP BY ProductID ORDER BY TopN DESC /cfquery 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
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
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 - 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. cfquery name=get_top_products_counts datasource=#session.dsn# SELECT TOP 3 COUNT(ID_PRODUCT) as thecount, ID_PRODUCT FROM ORDER_LINEITEMS GROUP BY ID_PRODUCT /cfquery = 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
