Is it possible to SELECT N first rows from database using ROWNUM if some rows have the same value?

Question

I'm trying to select N first rows from a database using ROWNUM. The problem is that if I want to select 6 first values of age with names of people with this age and some of the have the same value of age not every person will be shown. For example

name       age
Peter       15
Mark        22
Kelly       17
Mike        17
George      17

If I want to show people with 2 biggest values of age ROWNUM will show Mark and Kelly or Mike or George. Not all three of them. The expected result is:

Name        age
Mark         22
Kelly        17
Mike         17
George       17

Is it possible to get this result using ROWNUM? Not RANK, DENSE_RANK, JOIN, correlated subquery but ROWNUM?

Answer 1

The right solution dense_rank(), but you can do it with just row_number() and some subqueries:

select t.*
from t
where t.age in (select age
                from (select age
                      from t t2
                      order by age desc
                     ) x
                where rownum <= 2
               );

In Oracle 12+, you can simplify this:

select t.*
from t
where t.age in (select age
                from t t2
                order by age desc
                fetch first 2 rows only
               );