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

I have created one package with out procedures in package specification and in package body i have used 2 procedures. is it compile????

1 Answers  


Give SQL Query to find the number words in a sentence ? ex: 'ram charan singh' then ans:3 Answer:select length(trim('ram charan singh')) - length (replace (trim ( 'ram charan singh'),' ','')) +1 from dual The above query working properly when space between the words is only one &similar But ,If the space between the words is nonuniform. Ex:'ram charan singh is good' ans:5 i am not getting this answer using above query.

3 Answers  


What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?

1 Answers  


How to select some rows from a table in oracle?

0 Answers  


Differentiate between translate and replace?

0 Answers  






what is shell?

2 Answers   Microsoft,


What is the data type of dual table?

0 Answers  


What is recovery manager(rman) backup in Oracle?

0 Answers   MCN Solutions,


What is a nvl function?

0 Answers  


candidate key is subset of super key but not vice-verse explain

0 Answers  


In AP we done Customizations for Late Payments Charges. For Reporting Purpose What are the Documents Prepared for Customer Understanding??

0 Answers  


How can you Enforce Referential Integrity in snapshots ?

1 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)