Re: Converting MS Access SQL Top N Query To Equivalent Oracle SQL
From: <takveen_at_gmail.com>
Date: Fri, 10 Sep 2010 09:52:47 -0700 (PDT)
Message-ID: <37c1dec4-5d52-4018-a69e-c4189c88c12e_at_w4g2000vbh.googlegroups.com>
On Sep 10, 12:16 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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:113...
> andhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9...
> 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.shtml
Date: Fri, 10 Sep 2010 09:52:47 -0700 (PDT)
Message-ID: <37c1dec4-5d52-4018-a69e-c4189c88c12e_at_w4g2000vbh.googlegroups.com>
On Sep 10, 12:16 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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:113...
> andhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9...
> 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.shtml
The link you referenced in your reply is wrong and deprecated. Here is correct one:
http://www.progneer.com/wp/information_more_public.aspx?search_fd0=132497 Received on Fri Sep 10 2010 - 11:52:47 CDT