Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table emp (id) as select 2*level+49 from dual connect by level <= 10;
10 rows affected

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


ID


61

63

65

67

69


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


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


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