The only thing I can think of off the top of my head that MIGHT be able to help is 'LIMIT' in MySQL... even then I THINK you can only use that at the end of a query. But with nested selects, you might come up with something.
What type of database are you using? That might trigger the brain of some SQL gurus around here... but I'm afraid it's something that you're probably not going to get done in a single query. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -----Original Message----- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 9:21 AM To: CF-Talk Subject: RE: Group Query help. I'm no SQL expert but I don't know if what you're describing is possible. Retrieve a max of 5 rows for each group in the database. I think that's going to have to be done in CF. <!----------------//------ andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --------------//---------> -----Original Message----- From: Nomad [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 10:49 PM To: CF-Talk Subject: Group Query help. Hello! How do I limit the number of rows returned per group in a grouped query. My query: Select pid, Details, Brand from Products group by brand order by price This query returns: pid Details Brand 1 abcd Macromedia 2 efgh Macromedia 3 efgh Corel 4 abcf Corel 5 errer Adobe etc etc. What I want to achieve is to limit the max number of rows returned per group to say 5. So if there are 10 Macromedia products in my db only 5 rows would show up. Please note that my aim is not to limit the total number of rows returned with a Top statement or similar. Thanks in advance. Ben > Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. > http://www.houseoffusion.com/banners/view.cfm?bannerid=67 > > Message: http://www.houseoffusion.com/lists.cfm/link=i:4:221634 > Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 > Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 > Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11639.10644.4 > Donations & Support: http://www.houseoffusion.com/tiny.cfm/54 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227089 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
