Re: Converting MS Access SQL Top N Query To Equivalent Oracle SQL
Date: Fri, 10 Sep 2010 09:16:30 -0700 (PDT)
Message-ID: <ba51927a-9965-4827-82e6-cb6417da936f_at_l38g2000pro.googlegroups.com>
On Sep 10, 8:46 am, "takv..._at_gmail.com" <takv..._at_gmail.com> wrote:
> http://www.progneer.com/wp/information_more_public.aspx?search_fd0=13...
The FAQ has a much better answer. Sorry if analytics are too much rocket science for you. http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1137689100346245972 and http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:948366252775 are even better answers, they show how to do things with rownum and what ordering is really happening, or not happening.
Top ten of what, anyways? That is a stupid example. Top ten must include some kind of ordering as well as some determination of what is being ordered. If you depend on rownum, you may get different answers at different times. rownum is a pseudocolumn, so without ordering, you can get different results at different times with the same data. That is just so stupid, you obviously don't know the most basic relational theory or anything specific about Oracle. Rownum is evaluated after the data is selected from the db but before the order by statement is applied, so in a multiuser system with mvcc the data can be returned in a different order depending on whether Oracle finds it in an SGA buffer, gets it from disk, or has to rebuild it from undo, or some other more obscure things.
Oracle is not Access, and even in this degenerate and incomplete example does not translate directly.
Since you don't seem to get it, I'll yell: SQL IS NOT A PROCEDURAL LANGUAGE. Select * is bad programming practice, too.
jg
-- _at_home.com is bogus. Ever wonder where "flash crowd" came from? http://www.asimovs.com/201008/ref.shtmlReceived on Fri Sep 10 2010 - 11:16:30 CDT