Re: Finding the 10 smallest values in a column

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