about finding the nth value using a QUERY - urgent

about finding the nth value using a QUERY - urgent

Post by Bernhard Seelin » Thu, 25 Mar 1999 04:00:00



Hi ,

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

Bernhard Seeling

 
 
 

about finding the nth value using a QUERY - urgent

Post by Jurij Mod » Fri, 26 Mar 1999 04:00:00




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!!

Would you care to specify the release of Oracle RDBMS you are using
with the above statement? If it is anything less than 8i (aka 8.1.*)
then your homework isn't done yet, because ORDER BY is not allowed
inside a view in Oracle8.0 or lower. BTW, original poster specified
he/she is using PO7.

Quote:> Clement

Regards,

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

 
 
 

about finding the nth value using a QUERY - urgent

Post by Hans-Peter Sloo » Sat, 27 Mar 1999 04:00:00


Will this feature be in Oracle 8i?

> Would you care to specify the release of Oracle RDBMS you are using
> with the above statement? If it is anything less than 8i (aka 8.1.*)
> then your homework isn't done yet, because ORDER BY is not allowed
> inside a view in Oracle8.0 or lower. BTW, original poster specified
> he/she is using PO7.

> > Clement

> Regards,

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

 
 
 

about finding the nth value using a QUERY - urgent

Post by schon » Sat, 27 Mar 1999 04:00:00


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



> >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!!

> Would you care to specify the release of Oracle RDBMS you are using
> with the above statement? If it is anything less than 8i (aka 8.1.*)
> then your homework isn't done yet, because ORDER BY is not allowed
> inside a view in Oracle8.0 or lower. BTW, original poster specified
> he/she is using PO7.

> > Clement

> Regards,

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

 
 
 

about finding the nth value using a QUERY - urgent

Post by Jurij Mod » Sat, 27 Mar 1999 04:00:00




Quote:>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,

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

 
 
 

about finding the nth value using a QUERY - urgent

Post by Jurij Mod » Sun, 28 Mar 1999 04:00:00


On Fri, 26 Mar 1999 08:11:09 +0100, Hans-Peter Sloot


>Will this feature be in Oracle 8i?

Yes it is. In 8i you can use ORDER BY inside a view.

HTH,

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

 
 
 

1. Finding the nth largest or nth min. value

I want to do the following.
Retrieve data according to a filter (as in Where clause), sort it (as per
the order by clause) and then return only the row containg the 3rd largest
value in the sorted column or the row with 3rd minimum value in the sorted
column.

How should I frame the query? Can someone help me with an example?

Thanks in advance.
K.C.Sekhar

2. MFC DLL using DAO

3. Finding the Nth value?

4. Oracle JDBC driver for HP-UX

5. Question: Finding the nth row in a table

6. getting system:save error permission denied and cannot open save session with host during legatto backup

7. Select Nth value

8. HELP! I can only print ~75 records!

9. Get the nth value

10. Easier way to get nth highest value??

11. selecting the nth largest values in a column

12. Value List Nth item

13. Find Queries Not Using Indexes and Indexes Not Being Used