how will I find the first 5 highest salaried employees in
each dept in oracle.
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 |
I have created one package with out procedures in package specification and in package body i have used 2 procedures. is it compile????
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.
What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
How to select some rows from a table in oracle?
Differentiate between translate and replace?
what is shell?
What is the data type of dual table?
What is recovery manager(rman) backup in Oracle?
What is a nvl function?
candidate key is subset of super key but not vice-verse explain
In AP we done Customizations for Late Payments Charges. For Reporting Purpose What are the Documents Prepared for Customer Understanding??
How can you Enforce Referential Integrity in snapshots ?