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);

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)

Answer / samir kumar sahoo

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

Answer / mehul

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

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);

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

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

Answer / subodh tiwari

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

Answer / answer

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

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)

write sql query following source are EmpID, Salary 101 1000 102 2000 103 3000 I want the output format like following empid,sal,composite_sal 101 1000 1000 102 2000 3000 103 3000 6000

4 Answers   TCS,

How to create a single index for multiple columns?

0 Answers  

The table has 3 columns 4 rows. The output is which column has least null values( A COL SHOULD BE THE OUTPUT) Write the query plz. A B C ================ 1 NULL 2 NULL 3 4 NULL 5 6 7 NULL NULL O/P C = 2 4 6

2 Answers   TCS,

how to insert the image in table

2 Answers  

Explain oracle instance.

0 Answers  

What is E-R diagram ?

2 Answers   CGI, Keane India Ltd,

What is dynamic proxy?

0 Answers  

How to Select second Maximum salary in a Table ?

13 Answers   Oracle,

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

0 Answers  

can you explain performance tunning in oracle(sql,PL/SQL)

2 Answers  

What is a Data Dictionary ?

5 Answers  

1) Does oracle have any table which contain all the exceptions and it's code internally?

0 Answers   CTS,