0

I am searching for a query to find the details of highest paid employee in the entire organization. I am using oracle DB.

Queries which I tried are:

select * from EMP order by SAL desc FETCH FIRST 1ROW ONLY;

ERROR: SQL command not properly ended

select * from EMP order by SAL desc where rownum = 1;

ERROR: SQL command not properly ended

select * from EMP order by SAL desc LIMIT 0 , 1;

ERROR: SQL command not properly ended

GMB
  • 188,822
  • 23
  • 52
  • 100
beyou
  • 17
  • 1
  • 4

2 Answers2

0

There is an issue with all three statements.

Try this:

select * from EMP order by SAL desc FETCH FIRST ROW ONLY;

or use an analytical function ROW_NUMBER:

SELECT T.* FROM
    (SELECT
            T.*, ROW_NUMBER() OVER(ORDER BY SAL DESC NULLS LAST) RN
        FROM EMP) T
WHERE RN = 1

Cheers!!

Popeye
  • 34,354
  • 4
  • 8
  • 30
0

You could use:

SELECT * 
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM = 1

Please note that if rows have identifical salaries, ROWNUMBER will rank them randomly. To handle ties, you could use RANK() instead (or maybe DENSE_RANK(), depending on your needs):

SELECT * 
FROM (
    SELECT 
        e.*,
        RANK() OVER(ORDER BY sal DESC) rn
    FROM emp
)
WHERE rn = 1
GMB
  • 188,822
  • 23
  • 52
  • 100
  • ok.. the first highest is 5000rs. so if I give rownum=1 after desc then it works. But in order to find the 2nd highest where row number 2 and 3 both are 3000 after desc, then how can I modify my query? – beyou Sep 12 '19 at 09:42
  • @beyou: `ROWNUMBER` orders tied records randomly. I updated my answer with a solution that uses `RANK()` instead, which properly handle ties. – GMB Sep 12 '19 at 10:30