Query to get max and second max in oracle in one query ?

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

Question Posted / neelam

34 Answers
66470 Views
CTS, Keane India Ltd, TCS, Wipro, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Query to get max and second max in oracle in one query ?..

Answer / satish

SELECT SAL FROM (SELECT SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP) WHERE RANK=2;

Is This Answer Correct ?

0 Yes

0 No

Query to get max and second max in oracle in one query ?..

Answer / satish

SELECT SAL SECOND_MAX_SAL FROM (SELECT SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP) WHERE RANK=2;

Is This Answer Correct ?

0 Yes

0 No

Query to get max and second max in oracle in one query ?..

Answer / sunil

select sal from (select dense_rank() over(order by sal) rank
from emp) where rank in(1,2);

In above query you will get both highest and second highest
salary details even there are more people who are getting
highest and second highest salary.

Is This Answer Correct ?

0 Yes

0 No

Query to get max and second max in oracle in one query ?..

Answer / purna chowdary

Select Sal From Emp A
WHERE 1=(Select Count(Sal) From Emp B
WHERE B.Sal>A.sal)
GROUP By Sal Desc

Is This Answer Correct ?

0 Yes

0 No

Query to get max and second max in oracle in one query ?..

Answer / shareef

select ename,empno,sal,r from(select ename,empno,sal,dense_rank() over(order by sal desc) r from emp) where r=3; ----by using dense_rank()

Is This Answer Correct ?

0 Yes

0 No

Query to get max and second max in oracle in one query ?..

Answer / ajit

select max(sal),1 no
from emp
union
select sal,rn from (select sal,rownum rn from emp)
where rn = 2
order by 1 desc;

Is This Answer Correct ?

0 Yes

0 No

Query to get max and second max in oracle in one query ?..

Answer / asdasd

SELECT min(gross) FROM(SELECT distinct(gross) FROM
srbs_staff_pay_dtls ORDER
BY gross DESC) WHERE ROWNUM<=4;

Is This Answer Correct ?

2 Yes

3 No

Query to get max and second max in oracle in one query ?..

Answer / at_avrat_braveheart

select amcık from göt_tarlası

Is This Answer Correct ?

2 Yes

3 No

Query to get max and second max in oracle in one query ?..

Answer / abcd

for page break or form feed we use following
  /f (char)12
for nbsp;
(char)10  

Is This Answer Correct ?

1 Yes

2 No

Query to get max and second max in oracle in one query ?..

Answer / sarmistha mohanty

select * from (
select SAL,
row_number() over (partition by 1 order by SAL DESC)
as rank
from <table name> ) where rank in (1,2)

Is This Answer Correct ?

1 Yes

2 No

Post New Answer