CODEWITHSUNDEEP
sqloracle-databaserownum
Bro
Bro

Reputation: 57

Select 3rd salary from employee table

I would like to select 3rd salary from TABLE employees. I have written QUERY as you may see below but unfortunately it gives me 0 record. Can somebody help me on this topic ? I am using Oracle DBMS :) and here is an example of my database: SQL Fiddle

SELECT *
FROM 
  (SELECT ROWNUM, salary 
  FROM 
    (SELECT DISTINCT salary 
    FROM employees
    ORDER BY salary desc)
  )
WHERE ROWNUM = 3;

Upvotes: 0

Views: 204

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Seems to be Oracle (ROWNUM and no alias for the Derived Table). ROWNUM is calculated for the resulting rows, thus you can never filter for any ROWNUM greater than 1.

You need a Standard SQL ROW_NUMBER:

SELECT *
FROM
 ( SELECT salary
      ,row_number() over (ORDER BY salary desc) as rn 
   FROM employees
   GROUP BY salary
 ) dt
WHERE rn = 3;

The GROUP BY is equivalent to DISTINCT, but processed before the ROW_NUMBER while DISTINCT is processed after.

Edit:

If you want to use ROWNUM you must alias it:

SELECT *
FROM 
  (SELECT ROWNUM as rn, salary 
  FROM 
    (SELECT DISTINCT salary 
    FROM employees
    ORDER BY salary desc)
  )
WHERE rn = 3;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

In standard SQL, most databases, and Oracle 12C+, you can instead use:

SELECT DISTINCT salary 
FROM employees
ORDER BY salary desc
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

Upvotes: 0

Related Questions