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
Re: How to display 3rd highest salary to 5th highest salary from emp table
[b]>>display from 2nd highest salary to 5th highest salary from a table[/b]
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
Re: How to display 3rd highest salary to 5th highest salary from emp table
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;
Re: How to display 3rd highest salary to 5th highest salary from emp table
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);
Re: How to display 3rd highest salary to 5th highest salary from emp table
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>
[/code]
Re: How to display 3rd highest salary to 5th highest salary from emp table
Hi,
Here is the solution.
SELECT salary
FROM employee
ORDER BY salary DESC LIMIT 2, 3
I hope this will help you....
Re: How to display 3rd highest salary to 5th highest salary from emp table
select salary from emp_table order by salary asc limit 2,3;
Re: How to display 3rd highest salary to 5th highest salary from emp table
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
Re: How to display 3rd highest salary to 5th highest salary from emp table
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;
Re: How to display 3rd highest salary to 5th highest salary from emp table
we can get the solution for this queiry using 4 ways ...among 4 one solution is this [QUOTE=Ramadurga Prasad;60010]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;[/QUOTE]