Interested to Buy Any Domain ? << Click Here >> for more details...
Query to get max and second max in oracle in one query ?
- 34 Answers
- 64137 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 |
What is the difference between post-database commit and post-form commit?
What is transport network substrate (tns) in oracle?
What do you understand by a database object? Can you list a few of them?
how to code performance tuning of oracle PL/SQL? can any body send me the perfect answer?
How to load a large xml file?
1) What is dense ranking? 2) Difference between Substring and Instring? 3) Case and Decode? 4) Pseudo column? 5) View vs Materialized views? 6) SQl performance tuning?
What is a cursor variable?
What is index in Oracle?
1) What is ONE_SIZE_FITS_ALL approach? 2) Explain the Common & Variable Header of DATA FILE? 3) What are the Drawbacks to using OMF DB? and the Advantages? 4) List out the Advantages of Undo T.spaces over the Undo SEGMENT? 5) Difference between the Temporary tablespace with TEMPFILE and the Tablespace with TEMPORARY Keyword? 6) What are the situation extents are freeing for reuse.
How to drop a stored procedure in oracle?
What is the disadvantage of User defind function?
Explain table?
Oracle General 
