sql - Oracle - calculate number of rows before some condition is applied -

i know how many rows returned without rownum condition in given query:

select columns , [number of rows before applying rownum] numberofrows tables conditions , rownum < 100 

is possible without using subquery?

you can use analytic version of count() in nested query, e.g.:

select * (   select table_name,     count(*) over() numberofrows   all_tables   owner = 'sys'   order table_name ) rownum < 10; 

you need nest anyway apply order-by before rownum filter consistent results, otherwise random(ish) set of rows.

you can replace rownum analytic row_number() function:

select table_name, cnt (   select table_name,     count(*) on () numberofrows,     row_number() on (order table_name) rn   all_tables   owner = 'sys' ) rn < 10;