1

I have 15 records in a table and I want to fetch the last record from a table ?

This below query does not return any output for me … Am I doing anything wrong !!!

My query :

select * from employee where rownum=(select max(rownum) from employee);

One more thing I observed is that when I write this query it gives me first record as an output

select * from employee where rownum=1;

But when I write like this below it gives me nothing as an output ? why is it so ? It should give me second record ?

select * from employee where rownum=2;
yyy62103
  • 81
  • 5
  • 1
    Rows in a database table don't have any order. So `select * from employee where rownum=1;` doesn't give you the first row, but any row. `select * from employee where rownum=2;` doesn't give you any row, because as long as you don't pick a row that Oracle numbers 1, there ain't no row that Oracle can number 2. If you want the last row of a table, you must define what "last" means. If the table has a datetime or an ascending ID, you can use that. – Thorsten Kettner Aug 29 '22 at 17:47
  • As to `ROWNUM` in general: You don't need it. It was used in times when Oracle did not yet support standard SQL's `FETCH FIRST ROW(S)` clause and the `ROW_NUMBER` function. – Thorsten Kettner Aug 29 '22 at 17:54
  • 1
    A result set is unordered therefore `select * from employee` will get the rows in a non-deterministic (random) order. `select * from employee where rownum=1;` gets you one random row; it does **NOT** get you the first row - for that you would need an `ORDER BY` clause to have some concept of what is first or last. – MT0 Aug 29 '22 at 20:12

1 Answers1

2

ROWNUM is only assigned as rows are emitted from the query. Therefore, to use the strategy you're trying to use you'll need to make your query a little more complex:

WITH cteNumbered_employee_rows
  AS (SELECT e.*, ROWNUM AS RN
        from employee e)
SELECT *
  FROM cteNumbered_employee_rows
  where RN = (select max(RN)
                from cteNumbered_employee_rows);

(This ignores the fact that a query without an ORDER BY returns rows in whatever order the database finds to be most convenient. This unordered "last row" can be different between two runs of the exact same query).

Your second query is a common error in Oracle. It returns nothing because to get a row with ROWNUM = 2, there must first be a row with ROWNUM = 1 - however, since you're only looking for the row with ROWNUM = 2 it's never going to be found because no row with ROWNUM = 1 will be emitted. To make this work you need to use something like

SELECT *
  FROM (SELECT e.*, ROWNUM AS RN
          FROM EMPLOYEES e)
  WHERE RN = 2
Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • : can you tell me the meaning of this line : is only assigned as rows are emitted from the query – yyy62103 Aug 29 '22 at 18:02
  • @yyy62103 This is [documented](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726) with examples. Rownum is assigned when the row is sent to the consumer (a code that fetches rows from the cursor). – astentx Aug 29 '22 at 21:13
  • @yyy62103 - another way to say it is: the query generates the result set one row at a time. `ROWNUM` is assigned as each row is put into the result set, and the value ROWNUM is given will be one greater than the largest ROWNUM already in the result set, or 1 if nothing is in the result set yet. So if the only row that your query will allow into the result set is a row with ROWNUM = 2, it will never find that because every row which (potentially) might be assigned to the result set will have ROWNUM = 1 until a row has already been put into the result set. – Bob Jarvis - Слава Україні Aug 30 '22 at 20:26