Dept wise max sal

[kiss07]

Dear debas,

Fine and Thanks For Ur helping.

How can i find out department wise first 3 max salary?


O/P:

dept max(sal)
--------------------------------------------
HR 90000
89000
78000

TECH 67000
56000
45000

Regards,
Arun..

[chandu031]

Dear debas,

Fine and Thanks For Ur helping.

How can i find out department wise first 3 max salary?


O/P:

dept max(sal)
--------------------------------------------
HR 90000
89000
78000

TECH 67000
56000
45000

Regards,
Arun..

Hi,


SELECT
DEPTNO,SAL
FROM
( SELECT
DEPTNO,
SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO
ORDER BY
SAL DESC) MAX_SAL
FROM
EMP
)
WHERE
S < MAX_SAL

[chandu031]

Dear debas,

Fine and Thanks For Ur helping.

How can i find out department wise first 3 max salary?


O/P:

dept max(sal)
--------------------------------------------
HR 90000
89000
78000

TECH 67000
56000
45000

Regards,
Arun..

Hi,

Again using Rank function , your query will look like this:

SELECT
DEPTNO,SAL
FROM
( SELECT
DEPTNO,
SAL,
RANK() OVER (PARTITION BY DEPTNO
ORDER BY
SAL DESC) MAX_SAL
FROM
EMP
)
WHERE
MAX_SAL < 4

[debasisdas]

Ucan use the query

SELECT DEPTNO,SAL FROM ( SELECT DEPTNO, SAL, RANK() OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC) NUM FROM EMP ) WHERE NUM < 4
 

To customise (dynamically execute) replace the hard coding by a variable.

SELECT DEPTNO,SAL FROM ( SELECT DEPTNO, SAL, RANK() OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC) NUM FROM EMP ) WHERE NUM < (&n+1)