SELECT Statement returns ORA-00933: SQL command not properly ended

Question

I have the following SQL schema:

Create table Employee (Id int, Salary int);
Truncate table Employee;

insert into Employee (Id, Salary) values ('1', '100');
insert into Employee (Id, Salary) values ('2', '200');
insert into Employee (Id, Salary) values ('3', '300');

And executing following query against database:

SELECT Salary as SecondHighestSalary
FROM Employee
ORDER BY Salary
    OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY;

It works on Oracle XE 18but on leetcode.com (which version of Oracle they run I don't know) it returns following error:

ORA-00933: SQL command not properly ended

What am I doing wrong?

Answer 1

Three points. First, in earlier versions of Oracle, you can use window functions:

SELECT Salary as SecondHighestSalary
FROM (SELECT e.*,
             ROW_NUMBER() OVER (ORDER BY Salary DESC) as seqnum
      FROM Employee e
     ) e
WHERE seqnum = 2;

Second, this doesn't return the "second highest salary" -- under very reasonable interpretations of the phrase -- because the top salaries can be tied. If you actually want the second highest salary (as opposed to the salary of the person second in the list of ordered salaries), then use RANK() instead of ROW_NUMBER().

Third, you have these insert statements:

insert into Employee (Id, Salary) values ('1', '100');

Both id and Salary are declared as numeric. Don't mix types! The single quotes are not only unnecessary but misleading (although not harmful in this case):

insert into Employee (Id, Salary)
    values (1, 100);

Answer 2

Error message indicates that you are using version lower than Oracle 12c(most likely Oracle 11g XE).

OFFSET FETCH was introduced in version 12c. You could easily check it by executing:

select * from V$VERSION;

SELECT Salary as SecondHighestSalary
FROM Employee
ORDER BY Salary
    OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY;
-- ORA-00933: SQL command not properly ended

db<>fiddle