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 |
List the different type of joins?
what is top in tsql? : Transact sql
what are the different type of normalization? : Sql dba
What is an Exception ? What are types of Exception ?
Explain some predefined exceptions.
what are the advantages of using stored procedures? : Sql dba
ename empno deptno amar 1 10 akbar 2 20 anthonny 3 30 jonathan 4 40 write a procedure to dispaly the column values ina row separated by a deleimiter eg - input - select ename from emp '|' output - amar|akbar|anthony|jonathan input - select empno from emp '@' o/p - 1@2@3@4 input - select deptno from emp '/' o/p - 10/20/30/40 Pls answer this questn.
Is sql free?
what is a table called, if it has neither cluster nor non-cluster index? What is it used for? : Sql dba
What is the difference between a query and a report?
Explain the difference in execution of triggers and stored procedures?
What is string data type in sql?