• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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 ?
0
Shaunsmith
Asked:
Shaunsmith
  • 2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Try this:

select empno from (
    SELECT EMP_NO, row_number() over(order by emp_no) myrownum
) where myrownum=1;
0
 
XaelianCommented:
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
0
 
slightwv (䄆 Netminder) 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.
0
 
awking00Commented:
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now