We help IT Professionals succeed at work.

LIMIT not working in Oracle 10

SELECT DISTINCT EMP_NO
FROM EMPLOYEE E
ORDER BY EMP_NO
LIMIT 1

For some reason LIMIT does not seem to work in Oracle .I use dbvisualizer and I always get
" ORA-00933: SQL command not properly ended" when I use LIMIT .

I tried the simplest queries but I dont seem to get it .Even the db visualizer is not seeing it as a reserved work . Am I doing something wrong ?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Try this:

select empno from (
    SELECT EMP_NO, row_number() over(order by emp_no) myrownum
) where myrownum=1;
Commented:
Hi,

Limit doesn't work in Oracle.

SELECT DISTINCT EMP_NO FROM ( select * from EMPLOYEE E ORDER BY EMP_NO desc) where ROWNUM =1;

Here you have a full explanation of ROWNUM and how you can limit results with it.
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>FROM ( select * from EMPLOYEE E ORDER BY EMP_NO desc) where

I would not do a "select *" here since it can cause a full table scan.  Emp_no is likely indexed so just select it.
awking00Information Technology Specialist
CERTIFIED EXPERT
Commented:
selecting a distinct emp_no, ordering by emp_no (default ascending), and limiting it to one is basically the same as -
select min(emp_no) from employee

Explore More ContentExplore courses, solutions, and other research materials related to this topic.