Re: Confused in using ROWNUM in SQL query

ROWNUM is assigned to the result set of a select statement (before any ordering). Meaning, the first row will be #1, the second #2, etc. So don't think of it as a pseudo-column attached to a table.

Hope this insight helps in determining whether your sql statement is still correct.

Cheers,
BB

In article <l2RX5.48$1%2.3579_at_sjc-read.news.verio.net>,   "kush" <kdhakal_at_eline.com> wrote:
> hi all, I have following sql query in my application which runs
 against
> oracle 8.1.5 database.
> The whole idea of query is to get only top 5 records out of hundreds
 of
> records produced in
> the inner select statement.
> select * from (select distinct A.column1, B.column2, C.column1
> from tableA A, tableB B, tableC C
> where A.column3 between x and y
> and A.column1 = B.column1
> and C.column1(+) = A.column1
> order by A.column4 desc)
> where rownum < 6
> This query works just fine, only problem is its taking lots of time,
 this
> might be because first
> of all hundreds of records has to be selected in the inner select
 statement
> before selecting 5 out
> of it. Now, in order to increase the performance I tried to avoid
 nested
> select and put
> rownum < 6 in the first where clause, like below
>
> select distinct A.column1, B.column2, C.column1
> from tableA A, tableB B, tableC C
> where rownum < 6 and A.column3 between x and y
> and A.column1 = B.column1
> and C.column1(+) = A.column1
> order by A.column4 desc
> Its a lot faster then the first one, and its giving same results as
 in first
> one(I tried it
> in couple of cases). But i believe there is something wrong, for
 example:
> since we are using three
> tables, which table's rownum is it referring to in where clause? and
 also
> since we are using
> order by condition, rownum supposed to be used against the final
 selected
> rows after order by
> condition have been made. Now, my question is, is the oracle smart
 enough to
> know what I
> wanted? or is the second statement correct and same as first
 statement? if
> not where it went wrong
> and how can I correct it and how does rownum works?.
> Many thanks in advance.
>
> kush
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 07 2000 - 15:56:21 CST