Actually there doesn't seem to be a BOTTOM in Access or SQL Server. Instead of BOTTOM 50, select TOP 50 but reverse the order by clause (i.e. ORDER BY my DESC, column DESC, list DESC .
-----Original Message----- From: S.Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Wednesday, 31 August 2005 9:16 a.m. To: CF-Talk Subject: Re: Paging through records > In MS SQL and Access, there is the TOP clause to limit the > number of records, but I'm not sure if and how you can set > the first one. SELECT BOTTOM 50 * FROM mytable WHERE mytable_id IN (SELECT TOP 100 mytable_id FROM mytable ORDER BY my, column, list) ORDER BY my, column, list The order by clause must be the same in both the outer and sub-query. > In Oracle, there is ROWNUM... Yeah, but that's not how you get a set out of the middle with Oracle, 'cause if you specify a minimum value for rownum you don't get the expected results... well... not by itself... In oracle you use the MINUS clause, which is surprisingly efficient... but... I still don't recommend using it, just because I advocate db independance. SELECT * FROM mytable WHERE rownum < 100 ORDER BY my, column, list MINUS SELECT * FROM mytable WHERE rownum > 50 ORDER BY my, column, list ..... I haven't used this recently, so I'm not certain the order by clauses are in the right place, but that's the gist of it. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:216945 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
