On 8/30/05, S. Isaac Dealey <[EMAIL PROTECTED]> wrote: > > 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 >
note, the ORDER BY occurs after the WHERE clause, so this may not give the results you are expecting. DK > .... 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 > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:216968 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
