The ultimate result will be 50 records, but the NOT IN clause is very inefficient, especially when dealing with what is essentially a list of record ID's from 200- 1 million.
Bottom line, when using SQL Server there isn't much you can do to improve efficiency. You may want to play around with performance differences while using the SQL 'solution' vs. using CF's built in startrow/endrow attributes to the CFoutput tag. Depending upon usage variables, one may be better than the other. Mike -----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 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:219464 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
