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
60868 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 / sumit wadhwa

select distinct(a.colname) from table a where &n=(select
count(distinct(b.colname)) from table b where
a.colname<=b.colname);

from this query u can find out Nth MAX in a one query

eg
select distinct(a.sal) from emp a where &n=(select count
(distinct(b.sal)) from emp b where a.sal<=b.sal);

Is This Answer Correct ?

55 Yes

16 No

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

Answer / sumit wadhwa

for max

select a.sal
from emp a
where 1=(select distinct(count(b.sal))
from emp b
where a.sal<=b.sal);

for second max

select a.sal
from emp a
where 2=(select distinct(count(b.sal))
from emp b
where a.sal<=b.sal)

Is This Answer Correct ?

33 Yes

11 No

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

Answer / samir kumar sahoo

SELECT sal FROM(SELECT DISTINCT(sal) FROM employees ORDER
BY sal DESC) WHERE ROWNUM<=2;

Is This Answer Correct ?

23 Yes

2 No

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

Answer / mehul

select max(sal) from emp where sal not in ( select max(sal)
from emp)

Is This Answer Correct ?

29 Yes

12 No

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

Answer / rohit

The below query will provide the max and second max in
oracle in one query

select * from emp e
where 0 = (select count(sal) from emp
where e.sal < sal)
or 1 = (select count(sal) from emp
where e.sal < sal);

Is This Answer Correct ?

17 Yes

8 No

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

Answer / mats önnerby

--- Three level query
--- 1. Find and order all distinct salaries
--- 2. Pick the two top salaries
--- 3. Get all information about employees with that salary

select *
from emp
where sal in (
select sal from (
select distinct sal
from emp
order by sal desc)
where rownum <= 2);
---
--- Simple and straight forward but will return
--- several employees in case they have the same salary
---

Is This Answer Correct ?

12 Yes

3 No

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

Answer / 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

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

Answer / subodh tiwari

SELECT ENAME,SAL FROM (SELECT ENAME,SAL FROM EMP ORDER BY
SAL DESC) WHERE ROWNUM<=2

Is This Answer Correct ?

15 Yes

9 No

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

Answer / answer

NO NEED TO WATCH ANY OTHER ANSWER .ANSWER 11 IS GOOD.JUST
GO WITH IT

Is This Answer Correct ?

6 Yes

2 No

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

Answer / bhanu yadav

For Nth Max.

select distinct a.sal from emp a where &N= (select count
(distinct b.sal) from emp b where b.sal>=a.sal)

For Nth Min.

select distinct a.sal from emp a where &N= (select count
(distinct b.sal) from emp b where b.sal<=a.sal)

Is This Answer Correct ?

9 Yes

6 No

Post New Answer