LIMIT does not work with UNION

Question

What is wrong with below code?

SELECT  first_name, last_name
FROM    employees
UNION ALL
SELECT  first_name, last_name
FROM    dependents
ORDER BY  last_name
LIMIT 6 OFFSET 1;

Even a simple query with LIMIT doesn't seem to be working :

SELECT  first_name, last_name
FROM    dependents
ORDER BY  last_name
LIMIT 6 OFFSET 1;

Getting this error repeatedly: ORA-00933: SQL command not properly ended

Pரதீப் · Accepted Answer · 2018-01-21T14:29:22.287

1

I believe oracle does not support LIMIT. Try this

ORDER BY last_name
OFFSET 1 ROWS FETCH NEXT 6 ROWS ONLY;

Note this syntax supports from Oracle 12c

edited Jan 21 '18 at 14:29

answered Jan 21 '18 at 13:53

Pரதீப்

85,687
16
112
148

Thanks, I tried below and it works perfectly fine: SELECT first_name, last_name FROM dependents OFFSET 1 ROWS FETCH NEXT 6 ROWS ONLY; – Enthusiast Jan 21 '18 at 13:58
@Enthusiast - You are missing `Order by`, it is important to make the result consistent – Pரதீப் Jan 21 '18 at 14:28

score 1 · Answer 2 · answered Jan 21 '18 at 13:53

What is wrong with the code is that Oracle does not support limit. Period. The most recent versions support the ANSI standard fetch first <n> rows only.

Perhaps you intend:

SELECT e.*
FROM (SELECT e.*, ROW_NUMBER() OVER (ORDER BY last_name) as seqnum
      FROM (SELECT first_name, last_name FROM employees
            UNION ALL
            SELECT first_name, last_name
            FROM dependents
           ) e
      ORDER BY last_name
     ) e
WHERE seqnum BETWEEN 1 and 7;

Thanks Gordan, I'm a newbie and simply practicing the code on apex.oracle.com. Got my answer :) — Enthusiast, Jan 21 '18 at 13:56

LIMIT does not work with UNION

2 Answers2