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:219459
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
