Sure it works nicely, but if you have 1000 users pulling millions of rows each, that's going to put a large load on your server that really doesn't need to be there, not to mention network traffic if the DB server and CF server are separate. That's what I'm trying to avoid.
John Burns Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | Web Developer -----Original Message----- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 12:25 PM To: CF-Talk Subject: RE: SQL row select? Here's a post from a DBA I'm friends with: <QUOTE> Just pull the entire query over to CF and use query of queries, to grab your "paged" row sets. I work with record sets of millions and it works very nicely. </QUOTE> <!----------------//------ andy matthews web developer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --------------//---------> -----Original Message----- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 10:59 AM To: CF-Talk Subject: Re: SQL row select? Select top 50 * from table Where id not in ( select top 199 id from table order by date, alpha) order by date, alpha that works but what if you are paging through a million records as suggested before? wouldnt the inner query select the top 999,999 rows? IE Select top 50 * from table Where id not in ( select top 999999 id from table order by date, alpha ) order by date, alpha I think thats what Mike K was talking about. On 9/28/05, Andy Matthews <[EMAIL PROTECTED]> wrote: > Did no one see this post? After reading, and re-reading the original > post, it looks like this simple SQL statement is the answer (assuming > that MSSQL supports the LIMIT clause: > > SELECT * > FROM tablename > LIMIT #startrow#, #totalrecords# > > or > > SELECT * > FROM tablename > LIMIT 200, 50 > > <!----------------//------ > andy matthews > web developer > ICGLink, Inc. > [EMAIL PROTECTED] > 615.370.1530 x737 > --------------//---------> > > -----Original Message----- > From: Andy Matthews [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 8:57 AM > To: CF-Talk > Subject: RE: SQL row select? > > > I don't use MS SQL but doesn't it support the LIMIT keyword in the > SELECT statement? > > SELECT * > FROM tablename > LIMIT #startrow#, #totalrecords# > > or > > SELECT * > FROM tablename > LIMIT 200, 50 > > <!----------------//------ > andy matthews > web developer > ICGLink, Inc. > [EMAIL PROTECTED] > 615.370.1530 x737 > --------------//---------> > > -----Original Message----- > > >Ok, I feel like this should be something simple but my brain just > >isn't working this morning. Besides using CFOUTPUT with the startrow > >and maxrows attributes, is there a way in my SQL itself to specify > >that I want it to grab 50 rows starting at the 200th row. I'm trying > >to make a paged approach to a query that could potentially have about > >20,000 records returned. I'd much rather just have MS SQL return 50 > >rows at a time by telling it which row to start at rather than having > >it return a 20,000 recordset to CF and then only output 50. I'm > >hoping this is something easy and my brain just hasn't woken up yet. > >Any thoughts would be appreciated. > > > >John Burns > >Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | > >Web Developer > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219466 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
