SQL Server : Top 1 query to Oracle (11g) not 12c

Question

Here is a single SQL statement in SQL Server

SELECT 
    a.EMPNUM,
    (SELECT TOP 1 NAMEKO 
     FROM PA0001 
     WHERE COMPCD = a.COMPCD 
       AND EMPNUM = a.LASTUP 
     ORDER BY EDDATE DESC) AS LASTNM,
    (SELECT TOP 1 NAMEKO 
     FROM PA0001 
     WHERE COMPCD = a.COMPCD 
       AND EMPNUM = a.FRSTUP 
     ORDER BY EDDATE DESC) AS FIRSNM
FROM PA0000 a

Then, I tried to apply it to Oracle 11g (not Oracle 12c which supports the top n query)

SELECT 
    empnum,  
    (SELECT NAMEKO 
     FROM 
          (SELECT NAMEKO 
           FROM PA0001 
           WHERE COMPCD = a.COMPCD 
             AND EMPNUM = a.FRSTUP 
           ORDER BY EDDATE DESC) 
     WHERE ROWNUM = 1) AS FRSTNM,  
    (SELECT NAMEKO 
     FROM 
          (SELECT NAMEKO 
           FROM PA0001 
           WHERE COMPCD = a.COMPCD 
             AND EMPNUM = a.LASTUP 
           ORDER BY EDDATE DESC) 
     WHERE ROWNUM = 1) AS LASTNM
FROM PA0000 a

Then I got an error

ORA-00904: "A"."FRSTUP": invalid identifier.

How can I rewrite the query for Oracle?

possible duplicate of [Select range of rows in Oracle, excluding the rownum field](http://stackoverflow.com/questions/30321483/select-range-of-rows-in-oracle-excluding-the-rownum-field) — Lalit Kumar B, Jul 03 '15 at 08:52
You will get your complete answer about how to use ROWNUM in the post marked as duplicate. — Lalit Kumar B, Jul 03 '15 at 08:53
I made a function to use "execute immediate " and apply it to the query, thanks folks. — DaeHee Kim, Jul 10 '15 at 00:30

score 1 · Answer 1 · answered Jul 03 '15 at 08:48

1

First you need to add alias "a" to table PA0000 to avoid error ORA-00904: "A"."FRSTUP": invalid identifier.

answered Jul 03 '15 at 08:48

Roman Marusyk

23,328
24
73
116

Olafur Tryggvason · Answer 2 · 2015-07-03T09:09:04.350

1

using rownum=1 will select a random value not dependent on the order by clause (rather how the blocks are aligned on disk)

12c has this feature ( FETCH FIRST {n} ROWS ONLY clause )

In 11.2 and less, using row_number () is the most convenient way

Example of selecting the the latest object (single) created in the schema:

select *
  from (select user_objects.object_name,
               user_objects.object_id,
               user_objects.created,
               row_number () over (order by user_objects.created desc) as rn
          from user_objects) view_uo
 where view_uo.rn = 1

Viewing the latest 10 objects would be rn <= 10

Then just modify this to include your selects.

edited Jul 03 '15 at 09:09

answered Jul 03 '15 at 08:54

Olafur Tryggvason

4,761
24
30

It's not **LIMIT BY**, it's `FETCH FIRST n ROWS`. And yes you could do pagination using ROWNUM, see http://stackoverflow.com/a/30321788/3989608 – Lalit Kumar B Jul 03 '15 at 09:05
Correct, I was thinking of mysql. Haven't yet had any 12c in my production environment. Will update the answer – Olafur Tryggvason Jul 03 '15 at 09:07
Also with rownum, you have to know how it works to use it correctly. I have used it before, but I find that row_number() is more explicit and understandable for people unsure of (or new to) Oracle SQL. It's very easy to misunderstand how rownum works and get unexpected results. – Olafur Tryggvason Jul 03 '15 at 09:29
Thank you for your answer!! – DaeHee Kim Jul 07 '15 at 06:56

score 0 · Answer 3 · answered Jul 03 '15 at 10:01

As per my understanding from your question you can achieve the result by using level, connect by prior as follows:

SELECT EMPNO FROM EMP WHERE HIREDATE IN
(SELECT MAX(HIREDATE) FROM EMP WHERE LEVEL<=1 CONNECT BY PRIOR HIREDATE>HIREDATE GROUP BY LEVEL) ORDER BY HIREDATE DESC;

Hope this will solve your requirement...

SQL Server : Top 1 query to Oracle (11g) not 12c

3 Answers3