how to get the second max val for every group in a table

Categories >> Software >> Databases >> Oracle >> Oracle General
Suggest New Category

Question Posted / sivakumar katta

6 Answers
9713 Views
Verizon, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to get the second max val for every group in a table..

Answer / venkat

select * from (
select e.deptno,d.dname,e.sal,dense_rank() over (
partition by e.deptno
order by e.sal desc ) "RANK",
e.ename
from emp_test e,dept_test d
where e.deptno=d.deptno)
where rank=2

Is This Answer Correct ?

9 Yes

5 No

how to get the second max val for every group in a table..

Answer / abc

select max(sal) from emp_test where sal not IN(select
max(sal) from
emp_test group by deptno)group by deptno;

Is This Answer Correct ?

3 Yes

2 No

how to get the second max val for every group in a table..

Answer / suman rana

--venkat u r almost correct, since u have used "" (double
quotes) so column name will be come case sensitive.

select * from (
select e.deptno,d.dname,e.sal,dense_rank() over (
partition by e.deptno
order by e.sal desc ) RANK, e.ename
from emp_test e,dept_test d where e.deptno=d.deptno)
where rank=2

Is This Answer Correct ?

0 Yes

0 No

how to get the second max val for every group in a table..

Answer / suman rana

select * from emp e where 2 = ( select count(distinct sal)
from emp e1 where e1.deptno = e.deptno and E.SAL <= E1.SAL)

Is This Answer Correct ?

0 Yes

0 No

how to get the second max val for every group in a table..

Answer / ram posam

select * from (select deptno,sal,rank() over(partition by
deptno order by sal desc) k
from emp
group by deptno,sal)
where k=&n;

Is This Answer Correct ?

0 Yes

0 No

how to get the second max val for every group in a table..

Answer / indu

select max(sal) from salary where sal NOT IN (select max(sal) from salary)

Is This Answer Correct ?

9 Yes

13 No

Post New Answer