If your sql includes a sort, "SELECT * FROM foo WHERE rownum <= n order by z" won't work well because the where clause is applied prior to the order by clause.
This will work pretty uniformly for Oracle: "select * from (SELECT * FROM foo order by z) WHERE rownum <= n" Beth Bowden Phone: 919-541-3355 FAX: 919-541-3687 E-Mail: [EMAIL PROTECTED] Address: NIEHS, MD EC-03, P.O. BOX 12233, Research Triangle Park, NC 27709 -----Original Message----- From: Chris Blackwell [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 11:39 AM To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit Hi Doug, Yeah I think that would be a good idea, if you can find a way to implement it. From a little bit of googling it seems the syntax does vary a lot. Mysql: SELECT * FROM foo LIMIT n PGSql: SELECT * FROM foo LIMIT n MSSQL: SELECT TOP n * FROM foo Oracle: SELECT * FROM foo WHERE rownum <= n If you can find a way to do that, maybe the MySQL (and PGSql) implementation could include the optional offset value. query.setMaxRows(n [, offset]) wouldn't break anything ;) Keep up the good work Cheers, Chris -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Doug Hughes Sent: 23 February 2006 16:07 To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit As it regards to limiting, I may well change the maxrows to a db-specific mechanism. However, I won't add the full range of paging that the limit operator allows for in mysql. So, for all dbs, the maxRows would run a db-specific TOP or LIMIT (or whatever) to return only the maxRows number of rows. Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sean Corfield Sent: Thursday, February 23, 2006 10:59 AM To: [email protected] Subject: Re: [Reactor For CF] maxrows and limit On 2/23/06, Marlon Moyer <[EMAIL PROTECTED]> wrote: > They support it by other means. I know mssql supports TOP, which is very > much like LIMIT, but it does not support OFFSET, which, when it all comes > down to it is very important with db based paging. Reactor currently does not support limiting results returned by a query in the DB-specific ways you mention. However, it does now support paging on large record sets (assuming you wanted to page thru all 13,000 rows). -- Sean A Corfield -- http://corfield.org/ Got frameworks? "If you're not annoying somebody, you're not really alive." -- Margaret Atwood -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
