The closest MSSQL has is "top", as in "select top 50 * from ...". To do
paging, you either have to select the top N and scroll down to the
"page" you want, or, to do it entirely in sql, do nested selects, like
this:
-- fetch rows 200 to 250, in order
select *
from (select top 50 * From (select top 250 * from tablename order by
row_order) order by row_order desc)
order by row_order
It's lame, but in a pinch it works (assuming you have a column to sort
by, like "row_order" in my example).
Mark
-----Original Message-----
From: Andy Matthews [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 28, 2005 11:42 AM
To: CF-Talk
Subject: RE: SQL row select?
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:219456
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
