Use TOP or Rank when finding the first few or the most observations

Question

I have searched others' code and tried to customize in my case, but it seemed when the question was the "max", it worked. When it comes to find the top 100, it was not working. I am trying to get the first 100 people hired in the firm. I first tried TOP(100) and then RANK(), I guess they would both work. I was not super familiar with the syntax though. I wonder would anyone could kindly provide me with any suggestion on my code?

Thank you very much!

SELECT d.department_name, d.department_id, e.first_name, e.hire_date, e.salary 
from Dtable_department d join
     Etable_employee e
     on e.department_id = d.department_id
where hire_date = (select DENSE_RANK() OVER (PARTITION BY e.hire_date ORDER BY hire_date DESC) AS E_RANK_hire  WHERE rownum=100))
Group BY E_RANK_hire
order by E_RANK_hire

Possible duplicate of [How do I do top 1 in Oracle?](https://stackoverflow.com/questions/3451534/how-do-i-do-top-1-in-oracle) — Ori Marko, Jul 13 '19 at 17:19
I checked that. I changed the code to select * over (rank() order by hire_date DESC) from Etable_employee WHERE rownum=100, but it did not work. — Chen, Jul 13 '19 at 17:46

Barbaros Özhan · Accepted Answer · 2019-07-13T21:11:04.707

To get the first 100 people hired in the firm

First of all, Be careful about the tie cases are included within the results of both queries below. e.g. even if you have employee with equal hire date, they're included in the lists, meaning lists have at least 100 people.

If your Database version is 12c-, then you need to use a subquery in which to return the result of dense_rank() function :

select department_name, department_id, first_name, hire_date, salary
  from
  (
   select d.department_name, d.department_id, e.first_name, e.hire_date, e.salary,
          dense_rank() over ( order by hire_date ) as e_rank_hire
     from Dtable_department d 
     join Etable_employee e
       on e.department_id = d.department_id
  )
 where e_rank_hire <= 100 
 order by e_rank_hire;

If your Database version is 12c+, then you do not need to use a subquery by the sake of fetch clause :

select d.department_name, d.department_id, e.first_name, e.hire_date, e.salary
  from Dtable_department d 
  join Etable_employee e
    on e.department_id = d.department_id
order by hire_date
fetch first 100 rows with ties;

Pay attention for your case that using partition by clause is wrong and should be removed within the dense_rank() function's expression, and order of hire dates shouldn't be descending but ascending.

Demo for Top 10 Employee

Thank you! I did succeed using fetch but failed by using rownum. Thank you so much! May I ask why can't partition be used here? Because it is hire_date? Thanks!! — Chen, Jul 13 '19 at 20:32
you're welcome @Chen. `partition` is like a `group by`, for every step you turn back in a cyclic manner to the first value (1), unless hire_dates are equal consecutively. Btw, you even can use analytic function within order by clause such as `dense_rank() over ( order by hire_date ) ` but limiting to a certain value without a subquery is still problem for version 12- — Barbaros Özhan, Jul 13 '19 at 20:38

Use TOP or Rank when finding the first few or the most observations

1 Answers1

Linked