Hi Bobby, I am using SQl Server 2000. Ben Sorry for the delay in my reply.
----- Original Message ----- From: "Bobby Hartsfield" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Friday, December 16, 2005 3:34 AM Subject: RE: Group Query help. > 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 > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227112 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
