Re: Select the last n rows in oracle.
Selct top N stuff from aTable orderby query_seq desc. Rob On Thu, Mar 12, 2009 at 9:59 AM, Ian Skinner h...@ilsweb.com wrote: I would like to select the last n records from a table as defined by a sequence field. I tried SELECT stuff FROM aTable WHERE rownum n ORDER BY query_seq desc. But this just sorts the first n records in descending order where I want the last n records. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Select the last n rows in oracle.
TRY SELECT TOP N stuff FROM aTable ORDER BY query_seq desc Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged -Original Message- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: Thursday, March 12, 2009 9:59 AM To: cf-talk Subject: Select the last n rows in oracle. I would like to select the last n records from a table as defined by a sequence field. I tried SELECT stuff FROM aTable WHERE rownum n ORDER BY query_seq desc. But this just sorts the first n records in descending order where I want the last n records. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320441 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Select the last n rows in oracle.
There is no select top n in Oracle. A subquery is necessary: Select * FROM (SELECT stuff FROM aTable ORDER BY query_seq desc) WHERE rownum n http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html You can also use ROW_NUMBER(): http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/3/12 Rob Parkhill robert.parkh...@gmail.com: Selct top N stuff from aTable orderby query_seq desc. Rob On Thu, Mar 12, 2009 at 9:59 AM, Ian Skinner h...@ilsweb.com wrote: I would like to select the last n records from a table as defined by a sequence field. I tried SELECT stuff FROM aTable WHERE rownum n ORDER BY query_seq desc. But this just sorts the first n records in descending order where I want the last n records. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320443 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
James Holmes wrote: There is no select top n in Oracle. A subquery is necessary: Select * FROM (SELECT stuff FROM aTable ORDER BY query_seq desc) WHERE rownum n I was afraid of that... That is a huge dataset in that sub select. I mean really 10's of millions of records just to get the top 25. Oh well, at least it is only debugging|testing information and does not need to be high proformance for production purposes. I'll take a look at the Row_number() thing, that is new to me. P.S. Sure shows how many developers don't need to work with Oracles eccentricities. For all you SQL server types, Oracle unfortunately does not understand the TOP command. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320452 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
Try it out - Oracle is smarter than most DB's and you might be pleasantly surprised. Oracle will only sort the N rows, as it knows that overall you only want that many. It will not generate and sort the entire result set. Seriously, give it a go and check how long it takes. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/3/13 Ian Skinner h...@ilsweb.com: James Holmes wrote: There is no select top n in Oracle. A subquery is necessary: Select * FROM (SELECT stuff FROM aTable ORDER BY query_seq desc) WHERE rownum n I was afraid of that... That is a huge dataset in that sub select. I mean really 10's of millions of records just to get the top 25. Oh well, at least it is only debugging|testing information and does not need to be high proformance for production purposes. I'll take a look at the Row_number() thing, that is new to me. P.S. Sure shows how many developers don't need to work with Oracles eccentricities. For all you SQL server types, Oracle unfortunately does not understand the TOP command ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320453 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
How about this, using an expression for the ROWNUM test ... not sure it works, but some variation of this may be worth a try? SELECT stuff FROM aTable WHERE ROWNUM (COUNT(*) - n) ORDER BY query_seq DESC ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320454 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Select the last n rows in oracle.
That will sort a random n records, not take the top n sorted records. The subquery provides the sort first. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/3/13 Jason Fisher ja...@wanax.com: How about this, using an expression for the ROWNUM test ... not sure it works, but some variation of this may be worth a try? SELECT stuff FROM aTable WHERE ROWNUM (COUNT(*) - n) ORDER BY query_seq DESC ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320455 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
No, it shouldn't. There is no subquery, only the aggregate COUNT() function, and that's simply there to get the max number of rows. Max number - n = our starting number for last n, right? so I don't care about sorting within the COUNT(), only the aggregate value. The simple query is still: SELECT stuff FROM aTable ORDER BY query_seq DESC and the WHERE clause is then a simple 'ROWNUM greater than' statement, at least the way I read it. That will sort a random n records, not take the top n sorted records. The subquery provides the sort first. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320456 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Select the last n rows in oracle.
This should work for you. Select * From (Select rownum rnum, p.* From ( SELECT count(1) Over() totalCount, email, firstname FROM Carma_Contact ) p ) Where rnum = (totalCount - 50); Just replace the 50 with the number of rows back from the total that you want to select. Hope that helps. Nik On Thu, Mar 12, 2009 at 6:59 AM, Ian Skinner h...@ilsweb.com wrote: I would like to select the last n records from a table as defined by a sequence field. I tried SELECT stuff FROM aTable WHERE rownum n ORDER BY query_seq desc. But this just sorts the first n records in descending order where I want the last n records. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320457 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
Have you tried it? mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/3/13 Jason Fisher ja...@wanax.com: No, it shouldn't. There is no subquery, only the aggregate COUNT() function, and that's simply there to get the max number of rows. Max number - n = our starting number for last n, right? so I don't care about sorting within the COUNT(), only the aggregate value. The simple query is still: SELECT stuff FROM aTable ORDER BY query_seq DESC and the WHERE clause is then a simple 'ROWNUM greater than' statement, at least the way I read it. That will sort a random n records, not take the top n sorted records. The subquery provides the sort first. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320458 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Select the last n rows in oracle.
I don't have access to an Oracle database, so, no. Thankfully, I haven't had to develop on Oracle for the past several years, after spending several years splitting my time between Oracle and SQL Server. Seems like it should work, but I can't verify. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320468 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
I can verify in 9.i it doesn't work states group by function is not allowed there. On Thu, Mar 12, 2009 at 11:11 AM, Jason Fisher ja...@wanax.com wrote: I don't have access to an Oracle database, so, no. Thankfully, I haven't had to develop on Oracle for the past several years, after spending several years splitting my time between Oracle and SQL Server. Seems like it should work, but I can't verify. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320471 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Select the last n rows in oracle.
You could select the rows with sort descending, then use query of queryies to sort them back into the asc order couldnt you?if you're only manipulating 50 records or so the qofq wouldnt take long. Mike Kear Windsor, NSW, Australia http://afpwebworks.com AFP Webworks I tried SELECT stuff FROM aTable WHERE rownum n ORDER BY query_seq desc. But this just sorts the first n records in descending order where I want the last n records. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
You really don't need to use analytics in this case. The inline subquery presented by Mr. Holmes is the correct one. See http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/pseudocolumns009.htm#i1006297 Although this is for 11g, it holds for the other verisons as well ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320487 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
I'd much rather deal with Oracle's that SQL Server's. At least my experience has been it is far easier for me to deal with issues with Oracle v. ones with SQL Server. On Mar 12, 2009, at 10:06 AM, Ian Skinner h...@ilsweb.com wrote: P.S. Sure shows how many developers don't need to work with Oracles eccentricities. For all you SQL server types, Oracle unfortunately does not understand the TOP command. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320490 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Select the last n rows in oracle.
Oracle worries about the where clause before the sort (so it has to sort less rows). I filled ten rows in a table with value from 1 to 10 in random order, then ran this: SELECT STUFF FROM ATABLE WHERE ROWNUM 9 ORDER BY STUFF DESC If it works as you say, I should get 10-3 in order. I didn't. In my case I got 1-7 and 9. Adding the necessary subquery: SELECT * FROM( SELECT STUFF FROM ATABLE ORDER BY STUFF DESC) WHERE ROWNUM 9 I get the right results. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/3/13 Jason Fisher ja...@wanax.com: No, it shouldn't. There is no subquery, only the aggregate COUNT() function, and that's simply there to get the max number of rows. Max number - n = our starting number for last n, right? so I don't care about sorting within the COUNT(), only the aggregate value. The simple query is still: SELECT stuff FROM aTable ORDER BY query_seq DESC and the WHERE clause is then a simple 'ROWNUM greater than' statement, at least the way I read it. That will sort a random n records, not take the top n sorted records. The subquery provides the sort fir ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320495 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
