Interested to Buy Any Domain ? << Click Here >> for more details...
Query to get max and second max in oracle in one query ?
- 34 Answers
- 65571 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 ADDM Advisor in Oracle?
How we can able to import our own template (users designed MS-Word templates) which has many tabular columns; need to pass some values generate by Oracle-reports9i? Actually need to import more than 400 MS-Word templates into Oracle Reports-9i to minimize layout design in Reports.
If a table column has is UNIQUE and has NOT NULL, is it equivalent to a PRIMARY KEY column?
How oracle handles dead locks?
Where do we use decode and case statements?
what are the database links ?
Describe the different type of Integrity Constraints supported by ORACLE ?
What is the dynamic sql in oracle?
a query to select maxmun 3 salaries of employee table
How do we get field details of a table?
1.how to extract the second highest salary from emp table having sal as a column which contains the salary of all employee of an organisation.
How to define an anonymous procedure with variables?
Oracle General 
