Glad to know I'm not brain dead. It seems like they'd have some sort of an operator for this. Instead of a TOP, they need a MID(startrow, numrows) type function or something. Thanks for the replies about work arounds. I was hoping to avoid something like that, but I guess I can make it work that way.
John Burns Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | Web Developer -----Original Message----- From: Mike Klostermeyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 10:01 AM To: CF-Talk Subject: RE: SQL row select? A couple problems with this: 1. Typically you will want to sort on something other than the ID (date, alpha, etc.) 2. This query will return all records from the 200th record through the end of the table. If this table has a million records, you will be returning A LOT of data that you don't need. Sorry, but to my knowledge, there is nothing built into SQL Server that allows this type of filtering. Mike -----Original Message----- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 8:50 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 id) I think that will work. Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] -----Original Message----- From: Ken Ferguson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 9:49 AM To: CF-Talk Subject: Re: SQL row select? The syntax is different for each DB, but if I remember correctly -- MS SQL uses the TOP keyword. SELECT TOP(50) * FROM TABLENAME WHERE BLAH=BLAH Burns, John D wrote: >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:219429 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
