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
You didn't mention the version of Oracle.
On Oracle 12 there is a new low limiting clause that can be used:
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
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
order by Salary desc
fetch first 1 row only
Edit: Will work with newer Oracle versions.
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.
SQL> SELECT ename, sal FROM emp ORDER BY sal DESC
2 FETCH FIRST 1 row only;
Update Regarding duplicate rows
There is an option WITH TIES which will include the duplicate rows.
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;
SELECT name FROM emp
ORDER BY salary DESC
select * from emp order by salary DESC limit 1