SQL not correct end failure

Question

SELECT * FROM workers ORDER BY salary DESC LIMIT 2;

I have this SQL statement to get the salary of the two best paid workers but I'm getting a SQL command was not ended correctly.

EDITED:

SELECT  worker_id, last_name, job_id, salary
    FROM (SELECT w.*,
                rank() over (ORDER BY w.salary desc) rnk
          FROM worker w
         )
   WHERE rnk <= 5;

Welcome to [so]! The [code you added](https://stackoverflow.com/revisions/62516758/3) looks much like the code suggested in [an answer](https://stackoverflow.com/a/62516789/711006). If this is the solution, please [mark the answer accepted](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work), see [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers). If you are asking about the pasted code, please [edit] to clarify the question. — Melebius, Jun 23 '20 at 08:51

Littlefoot · Answer 1 · 2020-06-22T14:43:05.717

0

If your database doesn't support LIMIT clause, do it differently. For example:

select *
from (select w.*,
             rank() over (order by w.salary desc) rnk
      from workers w
     )
where rnk <= 2;

For example (based on Scott's EMP table):

SQL> select empno, job, sal from emp order by sal desc;

     EMPNO JOB              SAL
---------- --------- ----------
      7839 PRESIDENT       5000      --> two 
      7902 ANALYST         3000      --> highest
      7788 ANALYST         3000      --> salaries (5000 and 3000)
      7566 MANAGER         2975
      7698 MANAGER         2850
      7782 MANAGER         2450
      7499 SALESMAN        1600
      7844 SALESMAN        1500
      7934 CLERK           1300
      7521 SALESMAN        1250
      7654 SALESMAN        1250
      7876 CLERK           1100
      7369 CLERK           1000
      7900 CLERK            950

14 rows selected.

SQL> select *
  2  from (select w.*,
  3              rank() over (order by sal desc) rnk
  4        from emp w
  5       )
  6  where rnk <= 2;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO        RNK
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7839 KING       PRESIDENT            11/17/1981       5000                    10          1
      7788 SCOTT      ANALYST         7566 12/09/1982       3000                    20          2
      7902 FORD       ANALYST         7566 12/03/1981       3000                    20          2

SQL>

edited Jun 22 '20 at 14:43

answered Jun 22 '20 at 14:30

Littlefoot

78,293
10
26
46

There should also this guys listed which earns the most so there could be 3 – sqlhelp12 Jun 22 '20 at 14:36
This query shows *how* to do it. Change it, if you want. – Littlefoot Jun 22 '20 at 14:39
how to change?? – sqlhelp12 Jun 22 '20 at 14:40
I added *live* example; have a look. As of your question, I'm not sure I understand what you are asking. Based on the same sample data I posted (EMP table), what would you want to get as result? – Littlefoot Jun 22 '20 at 14:43
But when i use 5 instead of 2 it is not working – sqlhelp12 Jun 22 '20 at 14:55
It is working for me (obviously), so - you're doing something wrong. Please, post your sample data and query you wrote (by editing the question, not as a comment!) and I'll have a look. – Littlefoot Jun 22 '20 at 14:57
have done it... – sqlhelp12 Jun 22 '20 at 14:58
Are you joking? I know how `2` looks like when substituted by `5`. What am I supposed to do with that query? Where's sample data? – Littlefoot Jun 22 '20 at 15:09
The data is the same like in your table – sqlhelp12 Jun 22 '20 at 15:14
As I said: it *works* for me, so it should work for you as well (unless there's no data that satisfy WHERE condition, or your database version is too low and it doesn't support analytic functions, but then you'd get an error). – Littlefoot Jun 22 '20 at 15:15

score 0 · Answer 2 · answered Jun 22 '20 at 14:30

0

Oracle doesn't support limit. Use;

select w.*
from workers w
order by salary desc
fetch first 2 rows only;

This works in the most recent versions of Oracle. In earlier versions, you can use a subquery:

select w.*
from (select w.*
      from workers w
      order by salary desc
     ) w
where rownum <= 2;

answered Jun 22 '20 at 14:30

Gordon Linoff

1,122,135
50
484
624

1

"*works in the most recent versions*" is actually "in all supported versions" ;) – a_horse_with_no_name Jun 22 '20 at 14:46
@a_horse_with_no_name - many of us work for companies which still need to be dragged kicking and screaming into the twentieth century. – Bob Jarvis - Reinstate Monica Jun 22 '20 at 14:51
I know, this isn't chat, but - @Bob and the others - which **lowest** version do you have access to? I checked mine; 10.2.0.5.0 is the lowest. – Littlefoot Jun 22 '20 at 15:12
@Littlefoot: 11.2.0.3.0. And the newest is 11.2.0.4.0. But we're gonna upgrade to 19c Real Soon Now. Any day. Could happen any day now... – Bob Jarvis - Reinstate Monica Jun 22 '20 at 15:34
My newest is 12.2.0.1, @Bob; I don't know if there are any upgrade plans, but - you should wait. Version "20" looks *prettier* than "19" :) – Littlefoot Jun 22 '20 at 15:39

SQL not correct end failure

2 Answers2