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
