Query to find Nth MAX or MIN of a column

Writing a query for eccentric requirement is always exiting and challenging. Today I was asked to write a query to find Nth maximum or minimum value of a column in a table with one more column having a common data. (Not that much odd requirement right?).OK, I decided to write it directly without googling it. I manage to write this below query in couple of minutes. Check this query

select [column] from
(select [column],
rank() over(order by [column ]) rnk,
(select count(distinct [column]) from [table]) cnt
from [table] t ) where rnk=cnt+1-&N;

N will be the variable deciding the position of maximum data. For Nth minimum we can use the same query with desc clause in the order by section of the analytic function. Check the steps involved in this requirement

SQL> create table test_tbl(
2 id number,
3 num number);

Table created.

SQL> insert into test_tbl(
2 select rownum,dbms_random.random from task where rownum < 11);

10 rows created.

SQL> commit;

Commit complete.

SQL> select * from test_tbl;


ID NUM
---------- ----------
1 8576
2 566
3 3343
4 100
5 4
6 356
7 345
8 222
9 7465
10 122

10 rows selected.

SQL> select num from
2 (select num,
3 rank() over(order by num ) rnk,
4 (select count (distinct id ) from test_tbl) cnt
5 from test_tbl) where rnk=cnt+1-&n;
Enter value for n: 3
old 5: from test_tbl) where rnk=cnt+1-&n
new 5: from test_tbl) where rnk=cnt+1-3

NUM
----------
3343

SQL> /
Enter value for n: 2
old 5: from test_tbl) where rnk=cnt+1-&n
new 5: from test_tbl) where rnk=cnt+1-2

NUM
----------
7465

SQL> /
Enter value for n: 4
old 5: from test_tbl) where rnk=cnt+1-&n
new 5: from test_tbl) where rnk=cnt+1-4

NUM
----------
566

SQL> spool off

After writing this query I thought of checking the same functionality in the web.I found couple of queries. One of them from oracle site is

select * from tab1 a
where &n = (select count(distinct(b.col1))
from tab1 b where a.col1<=b.col1);

But if you compare the execution plan of the above two queries you will find the first one as much better. Guys, reading this can make a better query and add it as a comment in this blog!!