Using rank to select top 10 tuples in Oracle SQL

Question

I have the relation instructor(ID, name, dept name, salary).

The question in our assignment asks us to: Use the rank function in SQL to write a query to nd the id and name of those instructors in the top 10 most highly paid.

I'm able to rank the instructors through using select id, name, rank() over(order by(salary) desc) as sal from instructor order by sal

What is my next step in grabbing the top 10 instructors? Thanks.

Answer 1

I just figured it out actually, using rownum...

select id, name from(
  select id, name, rank() over(order by(salary) desc) as sal
    from instructor order by sal
  )
where rownum <=10

Hopefully this helps someone in the future!

Answer 2

Your solution is close to the answer but it's generally better to use the value given by the RANK() function:

select id
       , name
       , sal
from (
  select id
        , name
        , sal
        , rank() over(order by(salary) desc) as sal_rank
    from instructor
  )
where sal_rank <=10
/

The problem with rownum is that it arbitrarily truncates the result set. If we have a tie for tenth place it is usually important to know that fact. Using ROWNUM returns a single random record instead.

Sometimes ROWNUM - or the analytic ROW_NUMBER() - will be correct. It depends on the precise business requirements.