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

--------------Solutions-------------

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

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.

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>

Try this

SELECT name FROM emp
ORDER BY salary DESC
LIMIT 1

Try

select * from emp order by salary DESC limit 1

Category:sql Time:2018-07-26 Views:1
Tags: sql oracle

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.741 (s). 13 q(s)