Answer / yadunandan

select max(sal) from emp where sal<(select max(sal) from
emp)

Answer / anilchandu

Select * from emp x where 2=(select count(distinct sal)
from emp y where x.sal<=y.sal)


where x,y are alias names.

If you want 3rd heighest salary then just replace 2 by 3

Answer / manjeet

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

Answer / prabhudatta barick

--IN ORDER TO FIND THE NTH HIGHEST SALARY,
--WE JUST FIND THE SALARY THAT HAS EXACTLY N-1 SALARIES
GREATER THAN ITSELF---

--This is correlated subquery--
select empno,
ename,
sal
from scott.emp e
where &n-1=(select count(distinct b.sal) from scott.emp b
where b.sal>e.sal);

To get second highest salary put n value 2.

Answer / rakhi

SELECT *
FROM (
SELECT employee_id, last_name, salary,
RANK() OVER (ORDER BY salary DESC) EMPRANK
FROM employees)
WHERE emprank = 2;

Answer / amit bhagat

select min(a.sal) from (select disctinct d.sal from emp d
order by sal desc) a where rownum<=2

What are the extensions used by oracle reports?

0 Answers  

why dont we assign not null constraint as table level constraint.

0 Answers  

What is flashback in Oracle?

0 Answers   MCN Solutions,

What happens to the current transaction if a ddl statement is executed?

0 Answers  

Explain the difference between a procedure and a function? What do you understand by those terms?

0 Answers  

what are different types of deletes?

1 Answers  

How can we create the complete backup of data in the oracle.

0 Answers   IPEC, Satyam, SunTec,

What is difference between SUBSTR and INSTR?

1 Answers  

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

2 Answers  

how to clone 9i Database on to 10g Database.

0 Answers  

select trunc(round(156.00,-1),-1) from dual;

2 Answers  

How to use "in out" parameter properly?

0 Answers