Re: about finding the nth value using a QUERY - urgent

From: Jurij Modic <jmodic_at_src.si>
Date: Fri, 26 Mar 1999 22:29:10 GMT
Message-ID: <36fbe785.695296@news.siol.net>

On Fri, 26 Mar 1999 04:15:40 -0800, schong <schong_at_werple.mira.net.au> wrote:

>Try this instead :-
>
> select *
> from
> (
> select rownum rank, a.*
> from
> (
> select sno, marks
> from rankings
> group by marks
> ) a
> )

Again, this is not quite correct. Btw, you obviously forgot to add the WHERE condition to the outer select ("where rank = <the nth maximum value>") this time, but this is not the reason why I'm responding.

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,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Mar 26 1999 - 16:29:10 CST