Oracle Sql query

Question

There is an employee table having salary column. How to find the second highest salary among a list of employees?? Is there any convenient way to find the any nth number of salary ??

Please post the DDL statements for your tables, example input, expected output plus the queries you've tried so far. — Frank Schmitt, Feb 10 '14 at 07:45

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

Oracle has a rank function. Based on the result of this function you can select the nth number.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm

See also this question and answer: Nth max salary in Oracle

edited May 23 '17 at 11:49

Community

1
1

answered Feb 10 '14 at 07:50

Rene

10,391
5
33
46

score 0 · Answer 2 · edited Feb 10 '14 at 08:05

0

Second Highest Salary

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

Nth Highest Salary

SELECT * 
FROM Employee Emp1
WHERE (N-1) = ( 
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

edited Feb 10 '14 at 08:05

Nagaraj S

13,316
6
32
53

answered Feb 10 '14 at 07:57

Ajay Kumar

175
7

Pretty inefficient for large data sets – David Aldridge Feb 10 '14 at 08:00

score 0 · Answer 3 · answered Feb 10 '14 at 08:45

0

In Oracle 12c you have function 'NTH_VALUE' you can use for this.

answered Feb 10 '14 at 08:45

Wernfried Domscheit

54,457
9
76
110

Oracle Sql query

3 Answers3