Eww, I'm not diggin' that. I'll think on this some more. I'll also think on your suggestion Chris. The Oracle solution is the hardest. (Why the heck is Oracle so well respected and so wonkey at the same time?!)
Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bowden, Beth (NIH/NIEHS) [E] Sent: Thursday, February 23, 2006 11:37 AM To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit 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/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
