how to get second highest sal of emp table

Answers were Sorted based on User's Feedback



how to get second highest sal of emp table..

Answer / yadunandan

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

Is This Answer Correct ?    4 Yes 2 No

how to get second highest sal of emp table..

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

Is This Answer Correct ?    2 Yes 0 No

how to get second highest sal of emp table..

Answer / manjeet

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

Is This Answer Correct ?    1 Yes 0 No

how to get second highest sal of emp table..

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.

Is This Answer Correct ?    1 Yes 0 No

how to get second highest sal of emp table..

Answer / rakhi

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

Is This Answer Correct ?    2 Yes 2 No

how to get second highest sal of emp table..

Answer / amit bhagat

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

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More Oracle General Interview Questions

What is an oracle recycle bin?

0 Answers  


How to run create database statement again?

0 Answers  


How to install oracle odbc drivers?

0 Answers  


What is bulk collect in oracle?

0 Answers  


What is the difference between SQL and SQL Server?

2 Answers   CTS, TCS,






How to declare a local variable?

0 Answers  


Can you use a commit statement within a database trigger?

4 Answers  


How to call a stored function in oracle?

0 Answers  


I have two tables : COuntry,city I want all the cities in each country.Question on Cross Join.

3 Answers   Microsoft,


How to create a single index for multiple columns?

0 Answers  


What is Trace File ?

4 Answers   Thermotech,


What is blob data type in oracle?

0 Answers  


Categories
  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)