Limit Clause in Oracle 11g

Question

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.

use [ROWNUM](https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm) pseudo column ? FETCH is Oracle 12c+ i believe if i am not mistaken... — Raymond Nijland, Sep 23 '19 at 12:18
.. also what in cases on ties where the values are equal? As pagination/batching/limiting ideally requires a deterministic sort.. — Raymond Nijland, Sep 23 '19 at 12:22
actually I want to fetch the 2nd highest salary from the employee's table. — Rehan, Sep 23 '19 at 12:24
Why don't you upgrade to a modern Oracle version? Oracle 11 is no longer supported — , Sep 23 '19 at 12:27
*"I am new to databases and have started learning SQL"* As SQL is just a specification.. And `LIMIT` keyword in MySQL is a added feature which you can call a SQL dialect.. The best cross RDMS query to do this would be a corelated subquery from the old standards meaning the ANSI/iSO SQL 89/92 standards. `SELECT table_outer.*, (SELECT COUNT(*) FROM table AS table_inner WHERE table_inner.sal < table_outer.sal ORDER BY sal DESC) AS row_number FROM table AS table_outer WHERE row_number = 2` — Raymond Nijland, Sep 23 '19 at 12:40
@Rehan: tell your instructor he should upgrade his lesson to the 21st century. Oracle 18 is available as an express edition as well. And if that isn't a dedicated Oracle lesson, Postgres might be the better choice to begin with. And if it **is** a dedicated Oracle lesson he shouldn't be using an unsupported version. — , Sep 23 '19 at 12:45
@RaymondNijland: that might be portable but will be incredible slow. — , Sep 23 '19 at 12:46
*"that might be portable but will be incredible slow. "* indeed @a_horse_with_no_name i agree but i never said it would be the fasted method also Indexes might make it faster.. — Raymond Nijland, Sep 23 '19 at 12:52

Gordon Linoff · Accepted Answer · 2019-09-23T14:11:47.677

1

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).

edited Sep 23 '19 at 14:11

answered Sep 23 '19 at 12:19

Gordon Linoff

1,242,037
58
646
786

So I cannot set the offset just like in MySQL? – Rehan Sep 23 '19 at 12:25
@Rehan: replace offset with `seqnum >= ...` – Sep 23 '19 at 12:26
`select * from emp order by sal desc limit 1,1` this query returns the second highest salary of the employee. How should I write it in oracle? – Rehan Sep 23 '19 at 12:34
what does c.* means? – Rehan Sep 23 '19 at 14:01
@Rehan . . . It means to select all the columns from the table, view, or subquery represented by the table alias `c`. – Gordon Linoff Sep 23 '19 at 14:12
Thank you, so I got your answer as well. Basically we are assigning rownumbers toe each row after ordering it and filtering the quantity with rownum= 2. – Rehan Sep 23 '19 at 14:16
@Rehan . . . Yes, it doesn't use the built-in `rownum`, because `rownum = 2` would never return anything. – Gordon Linoff Sep 23 '19 at 14:20
@GordonLinoff one Last thing wanted to ask that why it gives error if I execute query like this: `select *, row_number () over (order by sal desc) as rank from emp` any logic behind it? I am trying to select all columns from emp with a new rank column... – Rehan Sep 23 '19 at 15:21
@Rehan . . . Use `select emp.*, . . .`. Oracle does not support `select *` with other expressions. – Gordon Linoff Sep 23 '19 at 16:13

score 0 · Answer 2 · edited Sep 23 '19 at 14:15

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

Limit Clause in Oracle 11g

2 Answers2