Use Top Or Rank When Finding The First Few Or The Most Observations

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 a

Solution 1:

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 ( orderby 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 <= 100orderby 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
orderby hire_date
fetchfirst100rowswith 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