select * from emp a where 2 > (select count(distinct sal) from emp b w

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

Question Posted / lokanath

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

Answer Posted / lokanath

select * from emp a where 2 > (select count(distinct sal)
from emp b where b.sal > a.sal)
or else use Top Window functions (or) Analatical functions
select * from
(
select empno,ename,sal,rank() over (order by sal desc)
test from emp
)
where test <= 2

Is This Answer Correct ?

12 Yes

4 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Can we write insert statement in function in oracle?

1081

Explain the difference between a procedure and a function?

1165

candidate key is subset of super key but not vice-verse explain

2068

What is recovery manager(rman) backup in Oracle?

1182

Explain what are synonyms used for?

1034

What is a tns service name?

1003

How data locks are respected in oracle?

1063

How to use like conditions in oracle?

1042

Please explain oracle left join with an example?

1041

Explain what are the different type of segments?

959

What do you mean by merge in oracle?

993

How to specify default values in insert statement using oracle?

1102

How to open a cursor variable?

1031

What are the roles of dba?

1065

What is bulk copy or bcp in oracle?

1117