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)

What are the different types of synonyms?

0 Answers  

how do u setup a replication site?

0 Answers  

17. Display the order number and average item cost for each order.

1 Answers   Wipro,

What is a system tablespace and when it is created?

0 Answers  

diff between DELETE and TRUNCATE?.

14 Answers   HCL, Yalamanchili Software,

I have a parent program and a child program. I want to write a statement in Exception Block of the parent program so that when the statement in the exception block is executed, the control goes to the next statement in the parent block bypassing the child block.How do i do that?

0 Answers   Mastek,

What is meant by recursive hints ?

1 Answers  

How can we manage the gap in a primary key column created by a sequence? Ex:a company has empno as primary key generated by a sequence and some employees leaves in between.What is the best way to manage this gap?

2 Answers   Infosys,

Can a formula column referred to columns in higher group ?

0 Answers   Oracle,

What is a subquery?

0 Answers  

how to select second mauximum value in a given table under salary column

6 Answers   Sapient,

What is redo log?

0 Answers