get last 5 records from a table-CodePudding

I have a query where the exact last 5 records are being fetched but i am unable to understand how it is working can anyone please help me in understanding it.

Select * from EMP a
where 5 > (select count(*) from EMP b where a.rowid < b.rowid );

CodePudding user response：

That query presumes that ROWIDs are sequential and represent order in which rows have been entered into a table. That doesn't have to be true. ROWIDs can change - for example, if you export the table (using Data Pump Export) and import it back. Therefore, it is pure luck you got correct result.

You should have a column which represents real order; for example, a column whose source is a sequence, ID which is generated automatically as identity column, a timestamp or something similar.

Then you'd be able to return rows that were last inserted. For example, based on Scott's EMP table & presuming that empno is sequential:

SQL> select empno, ename from emp order by empno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER   5      --> these rows are the ones we want to get 
      7876 ADAMS    4
      7900 JAMES    3
      7902 FORD     2
      7934 MILLER   1
 
14 rows selected.

If you apply the row_number analytic function, you get

SQL> select empno, ename,
  2            row_number() over (order by empno desc) rn
  3     from emp
  4  order by empno;

     EMPNO ENAME              RN
---------- ---------- ----------
      7369 SMITH              14
      7499 ALLEN              13
      7521 WARD               12
      7566 JONES              11
      7654 MARTIN             10
      7698 BLAKE               9
      7782 CLARK               8
      7788 SCOTT               7
      7839 KING                6
      7844 TURNER              5       --> see #5 here?
      7876 ADAMS               4
      7900 JAMES               3
      7902 FORD                2
      7934 MILLER              1

14 rows selected.

Then you'd use such a subquery:

SQL> with temp as
  2    (select empno, ename,
  3            row_number() over (order by empno desc) rn
  4     from emp
  5    )
  6  select empno, ename
  7  from temp
  8  where rn <= 5
  9  order by rn;

     EMPNO ENAME
---------- ----------
      7934 MILLER
      7902 FORD
      7900 JAMES
      7876 ADAMS
      7844 TURNER

SQL>

SQL> select *
  2  from (select empno, ename
  3        from emp
  4        order by empno desc
  5       )
  6  fetch first 5 rows only;

     EMPNO ENAME
---------- ----------
      7934 MILLER
      7902 FORD
      7900 JAMES
      7876 ADAMS
      7844 TURNER

SQL>

select * 
from (select empno, ename, rownum rn
      from emp
      order by empno desc
     )
where rn <= 5;

CodePudding user response：

Why not use the basics;

SELECT * FROM EMP a ORDER BY a.rowid DESC LIMIT 5

CodePudding user response：

Your current query:

Select * from EMP a
where 5 > (select count(*) from EMP b where a.rowid < b.rowid );

is using a correlated subquery to count how many rows in the same table have a 'lower' rowid value.

You can use the same correlated subquery to see the count for each row in the table:

select a.*, 
  (select count(*) from EMP b where a.rowid < b.rowid ) as num_with_higher_rowid
from EMP a;

If you had a table with 10 rows of data then that might look like:

ID	NUM_WITH_HIGHER_ROWID
51	9
53	8
55	7
57	6
59	5
61	4
63	3
65	2
67	1
69	0

The last row created is ID 69, and so that has the highest rowid (let's pretend for now - see @Littlefoot's answer for why this isn't necessarily or reliably true), which means there are zero rows with a higher rowid. For the penultimate rowid there is one row with a higher rowid, and so on.

Your query is comparing those counts with the number 5, so let's see what that comparison would show for each row and its count:

select a.*, 
  (select count(*) from EMP b where a.rowid < b.rowid ) as num_with_higher_rowid,
  case
    when (select count(*) from EMP b where a.rowid < b.rowid ) < 5 then 'Yes'
    else 'No'
  end as less_than_5_higher
from EMP a;

ID	NUM_WITH_HIGHER_ROWID	LESS_THAN_5_HIGHER
51	9	No
53	8	No
55	7	No
57	6	No
59	5	No
61	4	Yes
63	3	Yes
65	2	Yes
67	1	Yes
69	0	Yes

Your query is essentially filtering that result to only pick the rows that get 'Yes', by comparing the count value with the fixed number 5, to give:

ID
61
63
65
67
69

fiddle

However, as explained elsewhere, this is not a reliable way to find the most recently created rows, and also doesn't seem very efficient. Use one of the other suggested methods.