Oracle rownum returning wrong result

Question

I am trying a query to return only the latest row from table.

Initially I used max(id) in query But as I use sequence and my envoirnment is clustered, I cannot rely on sequence as its out of order. So I decided to order based on creation time and pick top row using rownum.

I used something like

SELECT  A.id
    FROM Table_A, Table_B B
    WHERE A.status = 'COMPLETED'
    AND B.name = 'some_name'
    AND A.id = B.id
    AND rownum = 1
    order by A.Creation_Time;

This some how returns me some wrong result say 42145. If I remove the rownum condtn the top record is differnet say 45343;

Answer 1

When using rownum with order by, you need to use a subquery. This has to do with the order of evaluation of the where and order by. So, try this:

SELECT t.*
FROM (SELECT A.id
      FROM Table_A JOIN 
           Table_B B
           ON A.id = B.id
      WHERE A.status = 'COMPLETED' AND B.name = 'some_name'
      ORDER BY A.Creation_Time
     ) ab
WHERE rownum = 1;

I should add: Oracle 12 supports fetch first 1 row only, which is more convenient:

  SELECT A.id
  FROM Table_A JOIN 
       Table_B B
       ON A.id = B.id
  WHERE A.status = 'COMPLETED' AND B.name = 'some_name'
  ORDER BY A.Creation_Time
  FETCH FIRST 1 ROW ONLY;