Why not do:
SELECT bulletins.bulletinID, bulletins.heading, bulletins.intro,
bulletins.createDate, bulletins.activated,
COUNT(bulletinNews.bulletinID) AS numItems
FROM bulletins, bulletinNews
WHERE bulletinNews.bulletinID = bulletins.bulletinID
GROUP BY bulletins.bulletinID, bulletins.heading, bulletins.intro,
bulletins.createDate, bulletins.activated
ORDER BY COUNT(bulletinNews.bulletinID)
Subqueries are always slower, so avoid them if you can.
-----Original Message-----
From: Gyrus [mailto:[EMAIL PROTECTED]]
Sent: Sunday, March 24, 2002 7:24 AM
To: CF-Talk
Subject: ordering by subqueries in SQL?
I'm using the following query to grab info on all news bulletins,
including the total number of news items (referenced in the
'bulletinNews' table) contained in each bulletin:
<cfquery name="getAllBulletins" datasource="#request.DB_DSN#"
dbtype="#request.DB_type#">
SELECT bulletinID, heading, intro, createDate, activated, (SELECT
COUNT(*) FROM bulletinNews WHERE bulletinNews.bulletinID =
bulletins.bulletinID) AS numItems
FROM bulletins
ORDER BY numItems
</cfquery>
I don't seem to be able to ORDER BY numItems. Is this an inherent
limitation of SQL (using Access 2000)? Can you not ORDER BY calculated
fields like this? Is there a way around it?
thanks,
- Gyrus
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- [EMAIL PROTECTED]
work: http://www.tengai.co.uk
play: http://www.norlonto.net
- PGP key available
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
______________________________________________________________________
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
