Very Computer

Hi ,

ORACLE evaluates ROWNUM before sorting rows
for  an ORDER BY clause .

Bernhard Seeling

Quote:>Try this :-

>  select *
>  from
>  (
>    select rownum rank, a.*
>    from
>    (
>     select sno, marks
>     from   rankings
>     order  by marks desc
>    ) a
>  )
> where rank = <the nth maximum value>;

> Now do you homework!!

Quote:> Clement

Quote:>Try this instead :-

>  select *
>  from
>  (
>    select rownum rank, a.*
>    from
>    (
>     select sno, marks
>     from   rankings
>     group   by marks
>    ) a
>  )

First, I belive your idea was to perform implicit sorting by using
GROUP BY instead of ORDER BY. The problem here is that this kind of
sorting is allways performed in *ascending* order, sou yo would get
the nth minimum value instead of nth maximum one. You would have to
cheat by grouping on negative values of marks.

Second, if you use a GROUP BY on MARKS, you must use some kind of
group operator on SNO, something like MAX(SNO) or MIN(SNO). Then you
would get into troubles if two or more SNOs have the same MARKS value.

And third, as was allready explained by Thomas Kyte in this forum,
there is no guarantie the optimiser will actualy perform a sort to
execute a grouping operation. So it would be at least advisable to
include a hint /*+ RULE */ or to force the use of RBO either at
session level or at instance level.

Mainly because of the second point, I would cahnge your query to look
something like this:

select /*+ rule +/ sno, -marks marks
 from
  (
    select rownum rank, a.*
    from
    (
     select distinct -marks marks, sno
     from   rankings
    ) a
  )
 where rank = 2
/

HTH,

Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer

HTH,

Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer