i need department wise top 2 employees salary.which logic
i will use
Answers were Sorted based on User's Feedback
Answer / neetika vardhan
SELECT dept, MAX(sal)
FROM EMP WHERE sal NOT IN (SELECT MAX(sal) FROM emp GROUP
BY dept)
GROUP BY dept
UNION
SELECT dept, MAX(sal)
FROM EMP
GROUP BY dept
Is This Answer Correct ? | 17 Yes | 8 No |
Answer / kavitha
SELECT distinct a.department_id, a.salary
FROM (SELECT department_id, salary,
DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC NULLS LAST) max_sal
FROM employees
WHERE department_id IS NOT NULL) a
WHERE max_sal <= 2
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / nag
select distinct(sal), deptno from
(select * from emp a where 1 = (select count(distinct sal)+1
from emp b where b.sal > a.sal and b.deptno = a.deptno) and
deptno is not null
union
select * from emp a where 2 = (select count(distinct sal)+1
from emp b where b.sal > a.sal and b.deptno = a.deptno)and
deptno is not null)
order by deptno,sal
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / naresh anumolu
SELECT * FROM(SELECT *,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK FROM EMP)A WHERE RANK<3
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / shreya
select level,deptno,max(sal)
from emp
where level in (1,2)
connect byy prior sal>sal
group by level,deptno
order by deptno
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / neo
select t1.empno, deptno, sal from emp t1 where t1.sal =
(select max(t2.sal) from emp t2 where t1.deptno = t2.deptno
group by deptno)
union
select t1.empno, deptno, sal from emp t1 where t1.sal =
(select max(t2.sal) from emp t2
where t2.sal < (select max(t3.sal) from emp t3 where
t3.deptno = t2.deptno and t1.deptno = t2.deptno )
group by deptno)
order by deptno, sal desc;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vikas kapoor
select deptno,sal from
(select deptno,sal,row_number() over(partition by deptno orderby sal) r from emp ) where r<3
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / ethayan
select foracid,clr_bal_amt from (select
foracid,clr_bal_amt,rank() over (order by clr_bal_amt desc
nulls last) as ranking
from tbaadm.gam order by a,clr_bal_amt nulls last)
where ranking=2
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / ashok kumar y
SELECT a.deptno, a.sal
FROM (SELECT deptno, sal,
DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) max_sal
FROM emp
WHERE deptno IS NOT NULL) a
WHERE max_sal <= 2 group by deptno,sal ;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / shyam
select * from
(select ename,job,sal,deptno,
dense_rank()
over(partition by deptno order by sal desc) densrank
from emp)
where densrank<=2;
Is This Answer Correct ? | 0 Yes | 0 No |
Can you skip header records while loading? : aql loader
Can you have a foreign key without a primary key?
How do we use distinct statement? What is its use?
what is a scheduled jobs or what is a scheduled tasks? : Sql dba
Why do we use set serveroutput on?
what is difference between delete and truncate commands? : Sql dba
i have a table with column sno with 30 records. i want to update this column by item by item in asp.net. i wantto enter new values into that from 1 to 30 how is it possible with backend c#
What is the difference between cross join and natural join?
I have done oracle 10g. I need a project knowledge. So if u please send a project how it should be done,Or you can send email link. I will be very grateful to u.
What are different types of functions in sql?
What is relationship? How many types of relationship are there?
What are the advantages of pl sql?