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 is Data Block ?

2 Answers  

write a sql query following source looking like below column1 column2 101,102,103 abc,def,ghi 1001,1002,1003 a,b,c i want the output column1 column1 101 abc 102 def 103 ghi 1001 a 1002 b 1003 c

1 Answers   KPIT,

How to fetch the row which has the max value for a column?

0 Answers  

In Exception handling if we are using the when others first then what happens . whether it will show the compiler error

2 Answers   HCL,

How to use select statement to count the number of rows in oracle?

0 Answers  

How can windows applications connect to oracle servers?

0 Answers  

Can a formula column be obtained through a select statement ?

0 Answers   Oracle,

Can we create more than one index on particular column?

1 Answers  

Briefly explain what is literal? Give an example where it can be used?

0 Answers  

How to rename a column in an existing table?

0 Answers  

What is a schema in oracle?

0 Answers  

does the query needs a hint to access a materialized view?

1 Answers