how will I find the first 5 highest salaried employees in
each dept in oracle.

Answers were Sorted based on User's Feedback



how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / murali mohan

Try This,

select empno,deptno,sal,dense_rank from (SELECT empno,
deptno,sal,
DENSE_RANK() OVER (PARTITION BY
deptno ORDER BY sal DESC NULLS
LAST) DENSE_RANK
FROM emp) tmp
where dense_rank<=5;

Regards,
Murali

Is This Answer Correct ?    21 Yes 3 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / ravindra reddy

select *from(select b.* from emp b order by sal desc)where rownum<=5 order by sal desc
------------------------------------------------------------
select *from(select b.*,rank() over(order by sal desc) from emp b)where rownum<=5

Is This Answer Correct ?    3 Yes 0 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / praveen marigaddi.

select distinct sal from emp a where 5 >(select count
(distinct sal) from emp where sal>a.sal) order by a.sal desc

Is This Answer Correct ?    6 Yes 6 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / karthik

select * from (select ename,eno,dept,sal,row_number() over
(partition by dept order by sal desc)top5 from emp) where
top5 <= 5

Is This Answer Correct ?    0 Yes 0 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / nagaraju nampally

select * from (select ename,sal,deptno,dense_rank()
over(partition by deptno order by sal)rnk from emp)x where
x.rnk<5

Is This Answer Correct ?    1 Yes 1 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / kanha

Select E1.* From(Select Ename,Deptno,Sal,
Dense_Rank()over
(
Partition By Deptno Order By Sal Desc
)"Top Sal"
From Emp)E1
Where "Top Sal"<=5 Order By Deptno,Sal Desc;

Is This Answer Correct ?    0 Yes 0 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / pawan

Select DISTINCT TOP 5 salary from emp_table1
UNION ALL
Select DISTINCT TOP 5 salary from emp_table2

for more details about UNION and UNION ALL check this: http://www.w3schools.com/sql/sql_union.asp

Is This Answer Correct ?    0 Yes 0 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / shareef

select * from(select emp.*,
dense_rank() over(partition by deptno order by sal desc) r
from emp) where r<=5;

Is This Answer Correct ?    0 Yes 0 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / sreeharibabu

select * from (select emp.*,row_number() over(partition by deptno order by sal desc) rnk from emp) where rnk <=5 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RNK
1 7839 X PRESIDENT 11/17/1981 5000.00 10 1
2 7782 X MANAGER 7839 6/9/1981 2450.00 10 2
3 7934 X CLERK 7782 1/23/1982 1300.00 10 3
4 700 X b 1234 9/9/2016 10:48:53 PM 1000.00 0.00 10 4
5 7369 X CLERK 7902 12/17/1980 8800.00 20 1
6 7788 X ANALYST 7566 4/19/1987 3000.00 20 2
7 7902 X ANALYST 7566 12/3/1981 3000.00 20 3
8 7566 X MANAGER 7839 4/2/1981 2975.00 20 4
9 7876 X CLERK 7788 5/23/1987 1100.00 20 5
10 7698 X MANAGER 7839 5/1/1981 2850.00 30 1
11 7499 X SALESMAN 7698 2/20/1981 1600.00 300.00 30 2
12 7844 X SALESMAN 7698 9/8/1981 1500.00 0.00 30 3
13 7521 X SALESMAN 7698 2/22/1981 1250.00 500.00 30 4
14 7654 X SALESMAN 7698 9/28/1981 1250.00 1400.00 30 5

Is This Answer Correct ?    0 Yes 0 No

how will I find the first 5 highest salaried employees in each dept in oracle...

Answer / jas

select a.sal,a.emp_no from employee a
where 5=(select distinct(count(*) from
employee b where a.sal> b.sal)

Is This Answer Correct ?    8 Yes 9 No

Post New Answer

More Oracle General Interview Questions

can u call user defined functions in select statements

2 Answers  


How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?

0 Answers  


Explain the use of full option in exp command.

0 Answers  


How do we switch from init.ora file to asp file?

2 Answers  


What is a data lock in oracle?

0 Answers  






I need to get the values of the previous quarter.how to do this?eg: if my cuurent month is may i need to get the datas of the month jan,feb,march.Can it be done in oracle.I tried with date function q but for the month jan its not retriving the previous quarter(oct-dec).how to solve this.plpz anyone help me?

2 Answers   Philips,


What privilege is needed for a user to query tables in another schema?

0 Answers  


Explain the importance of .pll extension in oracle?

0 Answers  


what is pragma?

2 Answers  


How to load data from external tables to regular tables?

0 Answers  


Select the Nth lowest value from a table?

4 Answers  


In what script is snap$ created? In what script is the scott/tiger schema created?

0 Answers  


Categories
  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)