Thread: How to display 3rd highest salary to 5th highest salary from emp table

Hi,
could any one please tell me how to display the higest salaries in between two numbers.
suppose say...display from 2nd highest salary to 5th highest salary from a table.
please clarify this

>>display from 2nd highest salary to 5th highest salary from a table
here is the query
select ename,job,sal
from
(
select rownum rn,ename,job,sal
from
( select * from emp order by sal desc )
)
where rn between 2 and 5;
-- where rn between lowerbound and upperbound

hi

You can take the help of inline query and rowid to solve these types of queries.
The query below may help you

select rownum,ename,salary
from (select ename,salary
from employee
order by salary desc)
where rownum between 3 and 5;

Try this..

select empname, salary
from (select empname, salary, dense_rank() over(order by salary) d_rank
from emp)
where d_rank in (3,4,5);

Check this:

Code:

SQL> select empno,salary,row_number() OVER(ORDER BY salary DESC) rn from emp;

     EMPNO     SALARY         RN
---------- ---------- ----------
         9         90          1
         8         80          2
         6         60          3
         4         40          4
         3         30          5
         2         20          6
         1         10          7

7 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  SELECT x.empno,salary FROM
  2  (select empno,salary,row_number() OVER(ORDER BY salary DESC) rn from emp) x
  3* WHERE rn >= &lower_limit AND rn <= &upper_limit
SQL> /
Enter value for lower_limit: 2
Enter value for upper_limit: 5
old   3: WHERE rn >= &lower_limit AND rn <= &upper_limit
new   3: WHERE rn >= 2 AND rn <= 5

     EMPNO     SALARY
---------- ----------
         8         80
         6         60
         4         40
         3         30

SQL>

Hi,

Here is the solution.

SELECT salary
FROM employee
ORDER BY salary DESC LIMIT 2, 3


I hope this will help you....

select salary from emp_table order by salary asc limit 2,3;

If use MS SQL database
It should be

select top 4 salary from employees where salary!=(select max(salary) from employees) order by salary desc

select e.* from(select empname,sal,dense_rank()
over(order by sal desc) rank from emp) e
where e.rank>=3 and e.rank<=5;

we can get the solution for this queiry using 4 ways ...among 4 one solution is this

Originally Posted by Ramadurga Prasad

select e.* from(select empname,sal,dense_rank()
over(order by sal desc) rank from emp) e
where e.rank>=3 and e.rank<=5;