CODEWITHSUNDEEP
sqloracle-database
Tirtha
Tirtha

Reputation: 33

Display the name of the maximum salary holder (WITHOUT USING A SUBQUERY)

Say there's a table

Name  Salary
Joe   4000
Steve 6000

I could just do this 

select name from emp where salary = (select max(salary) from emp);

but is there a way to do this without using a subquery?? Please help.

EDIT: Sorry I forgot to mention that I'm using Oracle 10g and LIMIT doesn't work on it

Upvotes: 3

Views: 774

Answers (5)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

In Oracle 12c, the top-n row limiting feature is introduced. Which allows to ORDER the rows without an additional subquery. So, no more dependency on ROWNUM and explicit sorting in a subquery.

For example,

SQL> SELECT ename, sal FROM emp ORDER BY sal DESC
  2  FETCH FIRST 1 row only;

ENAME             SAL
---------- ----------
KING             5000

SQL>

Update Regarding duplicate rows

There is an option WITH TIES which will include the duplicate rows.

For example,

SQL> insert into emp(empno, ename, sal) values(1234, 'LALIT', 5000);

1 row created.

SQL> SELECT ename, sal FROM emp ORDER BY sal DESC FETCH FIRST 1 ROWS WITH TIES;

ENAME             SAL
---------- ----------
KING             5000
LALIT            5000

SQL>

Upvotes: 1

krokodilko
krokodilko

Reputation: 36137

You didn't mention the version of Oracle.
On Oracle 12 there is a new low limiting clause that can be used:

  SELECT name
  FROM emp
  ORDER BY salary desc
  FETCH FIRST 1 ROWS ONLY;


There are examples in documentation: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC

On earlier versions it can't be done without using a subquery, but if you must then create a view:

CREATE VIEW emp_ordered AS
SELECT *
 FROM emp
ORDER BY salary desc;

and then query this view in this way:

SELECT * FROM emp_ordered
WHERE rownum <=1

Upvotes: 2

jarlh
jarlh

Reputation: 44805

ANSI SQL answer (no dbms specified):

select Name, Salary
from emp
order by Salary desc
fetch first 1 row only

Edit: Will work with newer Oracle versions.

Upvotes: 1

user3501409
user3501409

Reputation: 43

Try

select * from emp order by salary DESC limit 1

Upvotes: 0

KTAnj
KTAnj

Reputation: 1356

Try this 

SELECT name FROM emp
    ORDER BY salary DESC
    LIMIT 1

Upvotes: 0

Related Questions