Limit Clause in Oracle 11g

0
votes

I am new to databases and have started learning SQL. I am trying to convert a MySQL query to work with oracle database.

I am using Oracle 11g express edition and have tried many different answers but failed miserably. I don't know why oracle has no limit clause in its SQL

This is what it looks like in MYSQL:

select *
from emp
order by sal desc
limit 1,1

I have seen many answers for how to convert limit by using rownum in oracle but couldn't find how to write offset.

I got a solution from StackOverflow:

select * from emp
order by sal  desc
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

but it is giving the following error:

ORA-00933: SQL command not properly ended

Can anyone please suggest me some simple solution. Thanks in Advance.

2
sqloracle
use ROWNUM pseudo column ? FETCH is Oracle 12c+ i believe if i am not mistaken... - Raymond Nijland
.. also what in cases on ties where the values are equal? As pagination/batching/limiting ideally requires a deterministic sort.. - Raymond Nijland
actually I want to fetch the 2nd highest salary from the employee's table. - Rehan
Why don't you upgrade to a modern Oracle version? Oracle 11 is no longer supported - a_horse_with_no_name
@RaymondNijland: that might be portable but will be incredible slow. - a_horse_with_no_name

2 Answers

1
votes

In older versions of Oracle, you need a subquery:

select c.*
from (select c.*, row_number() over (order by c.points desc) as seqnum
      from customers c
     ) c
where seqnum = 2;

You will see examples that use rownum in the outer query:

select c.*
from (select c.*
      from customers c
     ) c
where rownum = 2;

However, that does not work, because rownum is incremented only when rows are placed in the result set. Window functions are the simplest solution in older versions of Oracle (new versions support fetch/offset).

0
votes

This is what Finally worked for me:

SELECT *
FROM   (SELECT sal, rownum AS rnum
        FROM   (SELECT sal
                FROM   emp
                ORDER BY sal desc)
        WHERE rownum <= 2)
WHERE  rnum > 1;

where 1 is offset

for more detail see: How to add offset in a "select" query in Oracle 11g?

Thanks to Jeffrey Kemp and Lalit Kumar B