SQL Query To Select Row Number, Rank, And Dese Rank

Use Analytic Functions

SELECT e.name as emp_name, d.name dept_name, e.salary,
      ROW_NUMBER() OVER(Partition by d.name ORDER BY e.salary DESC) AS row_num 
      FROM employee e join department d on e.dept_id = d.dept_id
     
 
SELECT e.name as emp_name, d.name dept_name, e.salary,
      RANK() OVER(Partition by d.name ORDER BY e.salary DESC) AS rank
      FROM  employee e join  department d on e.dept_id = d.dept_id
     

SELECT e.name as emp_name, d.name dept_name, e.salary,
      DENSE_RANK() OVER(Partition by d.name ORDER BY e.salary DESC) AS dense_rank 
      FROM  employee e join  department d on e.dept_id = d.dept_id

Without Using Analytic Function

select emp.*,
       (select count(*)
        from employee emp2
        where emp2.dept_id = emp.dept_id  

              and(emp2.salary > emp.salary or emp2.salary = emp.salary  

                   and emp2.emp_id <= emp.emp_id
                  )
       ) as "row_number",
       (select 1 + count(*)
          from employee emp2
          where emp2.dept_id = emp.dept_id and                 emp2.salary > emp.salary 
       ) as "rank",
       (select count(distinct salary)
        from employee emp2
        where emp2.dept_id = emp.dept_id and              emp2.salary >= emp.salary
       ) as "dense_rank"
from employee emp;

The Expert(Short Comedy Sketch)

The Expert(Short Comedy Sketch)

How annoying can you get?