Re: Finding the 10 smallest values in a column
Date: 1997/11/14
Message-ID: <64j9ig$g8f$1_at_tor-nn1.netcom.ca>#1/1
Hello,
First, I'm not certain if the following code will work as I don't have any way to test it this weekend; but, when I have to do something strange with Max / Min /Avg / Count functions I either try to use union queries / group by's / or nested query's as follows.
Again, I don't have any means to test the syntax so please beware that it might not work.
I would try something like this.
select a.val
from (select b.val from <table> b order by b.val desc)
where rownum <= 10;
I would hope that Oracle treats the sub-query as a table and returns the row numbers according.
By the way, Tom's comments are correct, a simple order by / rownum < 11 will not work. I've tried it before. Oracle seems to assign row numbers before the sort, not after.
The other comment about building a PL/SQL block is very cool as well, under certain circumstances it's probably the best way to go.
Hope this helps. Dave
Olivier wrote in message <01bcefae$f24b4b60$108624c3_at_default>...
>
>Is there an easy way of finding and inserting into another table the 10
>smallest of a column in a table?
>
> Thanks for your help.
>
Received on Fri Nov 14 1997 - 00:00:00 CET