How to get third one in a group in Oracle

Question

I have a table and assume it has 3 fields, they are f1, f2, f3, right now I want a SQL to group by f1 and f2 then sort f3 in each group in ASC or DESC order and query out the third one in each group in f3, how to write the SQL query. for example:

f1    f2    f3
A     A     2
A     A     3
A     A     1
A     B     4
A     B     6
A     B     2

in ASC order in each group, the query result should like this, get the third one:

A     A     3
A     B     6

in DESC order in each group, the query result should like this, get the third on:

A     A     1
A     B     2

is there a way to query the table and get a result like above in Oracle?

Answer 1

You could use the row_number window function to number each value of f3 (per group of f1 and f2) either in ascending or descending order, and then query the third one:

SELECT f1, f2, f3
FROM   (SELECT f1,
               f2,
               f3,
               ROW_NUMBER() OVER (PARTITION BY f1, f2 ORDER BY f3 ASC /* or desc */) AS rn
        FROM   mytable) t
WHERE  rn = 3

Answer 2

You can try to use Rank window function to make it.

SELECT f1, f2, f3
FROM   
(
    SELECT t1.*,
            RANK() OVER (PARTITION BY f1, f2 ORDER BY f3 ASC) AS rn
    FROM   T t1
) t1
WHERE rn = 3

Answer 3

this will work:

select * from (select f1,f2,f3,rank() over (partition by f1,f2 order by f3) 
as rank
from Table1 ) where rank=3;