Display the name of the owner of the maximum salary (WITHOUT USING A SUBKER)
Say the table there
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.
source to share
You didn't mention the Oracle version.
In Oracle 12 there is a new low limit clause that can be used:
SELECT name
FROM emp
ORDER BY salary desc
FETCH FIRST 1 ROWS ONLY;
There are examples in the documentation: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC
In earlier versions this cannot be done without using a subquery, but if you have to create a view:
CREATE VIEW emp_ordered AS
SELECT *
FROM emp
ORDER BY salary desc;
and then request this view like this:
SELECT * FROM emp_ordered
WHERE rownum <=1
source to share
The inserted top line bounding ., BOOK This allows the line without additional subquery. This way it no longer depends on ROWNUM and explicit sorting in the subquery. Oracle 12c
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 a WITH TIES option which will contain duplicate lines.
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>
source to share