There were a couple typos, but it works rather nicely. Try SELECT * FROM ( SELECT TOP 20 * FROM (SELECT TOP 220 * FROM table C ORDER BY C.column ASC) B ORDER BY B.column DESC ) A ORDER BY A.column ASC
Mike -----Original Message----- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 4:09 PM To: CF-Talk Subject: RE: SQL row select? Yup, tested it, works like a charm here mate. So unless you provide any more input as to why you think it doesn't work I won't be able to help you out. Taco Fleur - Pacific Fox an industry leader with commercial IT experience since 1994 . http://www.pacificfox.com - Web Design and Development > -----Original Message----- > From: Matthew Small [mailto:[EMAIL PROTECTED] > Sent: Thursday, 29 September 2005 6:58 AM > To: CF-Talk > Subject: RE: SQL row select? > > > Not only the IN method work, but yours did not, at least on > MSSQL. I tried it. > > Now that's not necessarily the most efficient solution > around, but if you have millions and millions of records to > parse through with CF, then you need a DBA to ensure proper > indexing and all that jazz, so you can let him write it. > > > Matthew Small > Web Developer > American City Business Journals > 704-973-1045 > [EMAIL PROTECTED] > > > -----Original Message----- > From: Taco Fleur [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 4:24 PM > To: CF-Talk > Subject: RE: SQL row select? > > 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 > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:219531 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
