Interested to Buy Any Domain ? << Click Here >> for more details...
how will I find the first 5 highest salaried employees in
each dept in oracle.
- 22 Answers
- 31977 Views
- IBM, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
What is the use of Control File ?
how the indexes are stored in the Oracle Database?
Explain oracle data types with examples?
interview questions with answer for cts
How i can handle exception in large code like 1000 line without distrubing the code or without exception handler sction?
How to write a query with a left outer join in oracle?
how to get count of tables in particular database in Oracle?
What do you mean by merge in oracle and how can you merge two tables?
Using the relations and the rules set out in the notes under each relation, write table create statements for the relations EMPLOYEE, FIRE and DESPATCH. You should aim to provide each constraint with a formal name, for example table_column_pk.
How to define a specific record type?
How index is implemented in oracle database?
What is translate in oracle?
Oracle General 
