i need department wise top 2 employees salary.which logic
i will use
Answers were Sorted based on User's Feedback
Answer / nitin tomer
Query without using analytic function:
SELECT dept_id, MAX(salary)
FROM EMPLOYEE_DEPT WHERE rowid NOT IN (SELECT MAX(rowid) FROM EMPLOYEE_DEPT GROUP
BY dept_id )
GROUP BY dept_id
UNION
SELECT dept_id, MAX(salary)
FROM EMPLOYEE_DEPT
GROUP BY dept_id;
using row_number() function:
SELECT NAME,DEPT_ID,SALARY,RNM FROM
(SELECT NAME,DEPT_ID,SALARY,ROW_NUMBER()OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RNM
FROM EMPLOYEE_DEPT)WHERE RNM<3;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / manish gupta
select deptno,sal from (select * from emp order by sal
desc) where rownum<3
union
select deptno,sal from (select * from emp order by sal
desc) where rownum<3 and deptno not in(20);
considering scott table in db.
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / priyank shah
SELECT * FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL
DESC)
WHERE ROWNUM < 3
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / mukesh kumar
SELECT * FROM (SELECT NAME,SALARY FROM EMP ORDER BY SALARY
DESC)
WHERE ROWNUM < 3
Is This Answer Correct ? | 1 Yes | 7 No |
Answer / ramya p
select deptno, max(sal) from (select * from emp order by
sal desc)
where rownum < 3
group by deptno
order by max(sal) desc;
Is This Answer Correct ? | 5 Yes | 12 No |
Answer / ramya p
Select * from emp where sal in
(Select * From (Select sal from emp order by sal desc)
Where rownum < 3) order by sal desc;
Is This Answer Correct ? | 6 Yes | 18 No |
what is 'mysqlcheck'? : Sql dba
Write a query to display the current date in sql?
Can a primary key be a foreign key?
Why do we need cursor in pl sql?
What is where clause in sql?
in sql table follw columns r their vehicle_no|servicetype|date|location| 1234 |1FS |1-JAN|PUNE| 4561 |2FS |5-JAN|PUNE| 1234 |PS |7-JAN|PUNE| 1234 |PS |15-JAN|MUMB| i want o/p below vehicleno|1fs|1fsdate|1fslocation|1ps|1PSDATE|1PSLOC|2PS|2PS DATE|2PSLOCA e.g 1234|1FS|1JAN|PUNE|1PS|7JAN|PUNE|2PS|15JAN|MUMBAI PLS help me out to execute using sql query
Can triggers stop a dml statement from executing on a table?
How to display all Friday's in a year with date?
Explain lock escalation? : Transact sql
Can cursors be part of a trigger body?
Is pl sql still used?
Explain constraints in sql?