CODEWITHSUNDEEP
sqloracle-databaseplsqlgreatest-n-per-group
Durairaj s
Durairaj s

Reputation: 193

How to select nth row from table using rownum

SELECT emp_id INTO high_payed_emp_id
FROM (SELECT emp_id  from cursor_table ORDER BY emp_salary DESC)
WHERE rownum = 1;

I am trying to get the emp_id of max. payed employee error message:ORA-00905: missing keyword

Upvotes: 0

Views: 140

Answers (4)

Mohammad Yasir Arafat
Mohammad Yasir Arafat

Reputation: 705

This seems to me the easiest approach to select the employees having max salary in Oracle:

select emp_id from cursor_table where emp_salary 
= (select max(emp_salary) from cursor_table)

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

Yor need correct sql syntax

 insert INTO  high_payed_emp_id (emp_id)
 select * from
 (
 SELECT emp_id
 FROM cursor_table ORDER BY emp_salary DESC
  )
 WHERE ROWNUM <= 1;

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Use FETCH FIRST instead, add WITH TIES to get both if there are two (or more) with the same max salary:

SELECT emp_id
FROM cursor_table
ORDER BY emp_salary DESC
FETCH FIRST 1 ROW WITH TIES

Upvotes: 5

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

SELECT emp_id INTO high_payed_emp_id
FROM (SELECT emp_id  from cursor_table 
WHERE emp_salary = (select max(emp_salary) from cursor_table))a

Upvotes: 1

Related Questions