Query to get max and second max in oracle in one query ?
- 34 Answers
- 60280 Views
- CTS, Keane India Ltd, TCS, Wipro, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
SELECT sal FROM(SELECT DISTINCT(sal) FROM employees ORDER
BY sal DESC) WHERE ROWNUM<=2;
| Is This Answer Correct ? | 23 Yes | 2 No |
Answer / mehul
select max(sal) from emp where sal not in ( select max(sal)
from emp)
| Is This Answer Correct ? | 29 Yes | 12 No |
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 |
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 |
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 |
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 |
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 |
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 |
Differnce between view and index
How to select an oracle system id (sid)?
How can you Enforce Referential Integrity in snapshots ?
what is dynamic SGA and static SGA
What are the different types of trigger and explain its various uses and functions?
How to get a create statement for an existing table?
What is the minimum client footprint required to connect c# to an oracle database?
What happens to the indexes if a table is recovered?
What are the commands youd issue to show the explain plan for select
How can I get column names from a table in oracle?
what are archived logs?
How will you differentiate between varchar & varchar2?
Oracle General 
