how can find the second max sal for every group(i.e i want group the data base

how can find the second max sal for every group(i.e i want
group the data based on key and find the second max sal for
every group

Question Posted / sivakumar

2 Answers
5442 Views
Verizon, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how can find the second max sal for every group(i.e i want group the data based on key and find th..

Answer / sudipta santra

select max(sal),group_nm from
( select sal,dense_rank over (order by sal desc) rank,group_nm
from emp )
where rank=&n
group by group_nm;

Note: here &n is the bind variable which holds value from
the user, based on the requirement either 2nd max or 3rd max
or 5th max.

Is This Answer Correct ?

2 Yes

1 No

how can find the second max sal for every group(i.e i want group the data based on key and find th..

Answer / nathan

SELECT *
FROM (SELECT deptno, sal,
DENSE_RANK () OVER (PARTITION BY deptno ORDER
BY sal DESC)

ranks
FROM emp)
WHERE ranks = 2;

Is This Answer Correct ?

2 Yes

1 No

Post New Answer