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 |
Why do we need view in sql?
Explain the order of sql statement execution?
What has stored procedures in sql?
What is sql mysql pl sql oracle?
What are the three pl sql block types?
What is a record in a database?
What is difference between mysql and postgresql?
How do you pronounce sql?
Can we insert delete data in view?
Which type of cursor is used to execute the dml statement?
write a query that returns first characters of each word in Oracel/Sql pl sql
explain advantages of myisam over innodb? : Sql dba