I would not expect an answer like that from a proper DBA. Not sure if we are talking about MS SQL or MySQL, but the idea is to work with subqueries.
SELECT * FROM ( SELECT * TOP 20 FROM (SELECT TOP (200 + 20) yourColumn1, yourColumn2 FROM yourTable C ORDER BY C.yourOrderColumn ASC) B ORDER BY B.yourOrderColumn DESC ) A ORDER BY A.yourOrderColumn ASC Where 200 is for example the current row you are on and 20 is the number of records you want to retrieve. The idea is to first get TOP currentRow + numberOfRowToRetrieve Then order descending and get the top 20 Then order ascending and select all This way you only transfer from the DB over the network what you need to, and the query is not inefficient, especially if you got the right indexes. Now, its early here, so I might have made a mistake, but you get the general idea. I think the previous sample with IN does not work, especially not efficient. Taco Fleur - Pacific Fox an industry leader with commercial IT experience since 1994 . http://www.pacificfox.com - Web Design and Development > -----Original Message----- > From: Andy Matthews [mailto:[EMAIL PROTECTED] > Sent: Thursday, 29 September 2005 2:25 AM > 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 > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:219512 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
