from keyword error in oracle apex site

Question

I have tried checking all the duplicate answers provided in this site but couldn't able to come up with rectifying my error could you guys help me out on this?

Getting this error

ORA-00923: FROM keyword not found where expected

SELECT TOP 1 sal FROM emp WHERE sal in
(SELECT DISTINCT TOP 3 sal FROM emp ORDER BY sal DESC)
ORDER BY sal ASC;

I am solving this using oracle apex site

`TOP x` is not Oracle SQL syntax, it is most associated with SQL Server syntax. [how to do top](https://stackoverflow.com/questions/3451534/how-do-i-do-top-1-in-oracle) — Andy G, Dec 28 '17 at 09:32
Why do you first select top 3, and then top 1? Why not directly top 1? That would, then, simply be MAX(SAL). — Littlefoot, Dec 28 '17 at 09:48
@Littlefoot - looks like that code is looking for the third-lowest salary, from the asc/desc mix, not the highest (or lowest)? — Alex Poole, Dec 28 '17 at 10:01

score 2 · Answer 1 · answered Dec 28 '17 at 10:10

You appear to want to find thew record with the third-lowest salary.

You can use a subquery to assign an analytic rank to each salary, and then filter on that:

select * from (
  select e.*, dense_rank() over (order by sal desc) as rnk
  from emp e
)
where rnk = 3;

From 12c you can use the offset and fetch syntax to do the same thing:

select * from emp
order by sal desc
offset 3 rows
fetch first row only;

You need to decide how to deal with ties though - if more than one employee shares that third-lowest salary; or even if the lowest and second lowest are awarded to more than one person. Depending on what you want to happen you can look at variations of the 12c row-limiting syntax, and other analytic functions - row_number(), dense_rank() - and their windowing options.

Only by making it more complicated. I guess you could use lead/lag in a subquery with an offset of 3, and then filter on that being not null, and then find the first row from that subset. But why make it more difficult? — Alex Poole, Dec 28 '17 at 10:23

score 1 · Accepted Answer · answered Dec 28 '17 at 09:53

1

I was able to get my query now

SELECT A.* FROM emp A where 3 =(SELECT COUNT(DISTINCT sal) FROM emp B WHERE A.sal <= B.sal);

answered Dec 28 '17 at 09:53

Johny_Bravo

159
1
2
12

score 1 · Answer 3 · answered Dec 28 '17 at 10:08

1

Yet another option, using analytic function:

WITH ranks
     AS (SELECT empno, DENSE_RANK () OVER (ORDER BY sal DESC) rnk FROM emp)
SELECT e.*
  FROM emp e, ranks r
 WHERE e.empno = r.empno 
   AND r.rnk = 3;

answered Dec 28 '17 at 10:08

Littlefoot

131,892
15
35
57

what about lead and lag can it be done by using them – Johny_Bravo Dec 28 '17 at 10:13
Maybe, but I'd say that these aren't supposed to be used for such a purpose. – Littlefoot Dec 28 '17 at 10:24

from keyword error in oracle apex site

3 Answers3