Question

(SQL Coding)

Create a read only view called view_emp_salary_rank_ro that selects the last name and salary from the o_employees table and ranks the salaries from highest to lowest for the top three employees.

Answer 1

Consider the following table:
Employee:

emp	salary
A	23000
B	31000
C	24500
D	35000
E	28500
F	31500
G	39800
H	51000
I	39800

Query :

select * from(
select ename, sal, dense_rank() 
over(order by sal desc)r from Employee) 
where r=&n;

To find to the 2nd highest sal set n = 2
To find 3rd highest sal set n = 3 and so on.

DENSE_RANK :

1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.
2. This function accepts arguments as any numeric data type and returns NUMBER.
3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.

Give feedback