No, you are not missing something, just an early morning for me. Sorry to misinform.
Mike -----Original Message----- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 9:04 AM To: CF-Talk Subject: RE: SQL row select? 1) So sort on something else ( Select top 50 * from table Where id not in ( select top 199 id from table order by date, alpha) order by date, alpha 2) This query will return 50 records. Am I missing something? Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] -----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 > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:219431 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
