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
