[pascalmahe]

Could you please develop how to do paging with window functions?

It's been some time since I've done serious work with SQL yet I remember that all paging solutions I've found (eg. top results on SO) are always platform-specific so having a platform-independent way of doing it would mean I could finally try to remember it.

[forinti]

Try this:

    select ord, table_name, first_name, last_name, total_rows 
    from (
        select table_name,
               row_number() over (order by table_name asc) ord,
               count(1) over () total_rows,
        from all_tables 
        where table_name like 'B%'
        order by table_name desc
    ) where  ord between 10 and 19
    order by ord

This is basically what Oracle Apex does. You can do this without window functions, but it gets a bit more complicated.

I like to add total_rows so that I can show a "rows 10 - 19 of 81" header. You can also get the very first and last values by adding theses columns:

    first_value(table_name) over (order by table_name asc rows between unbounded preceding and unbounded following) first_name,
    last_value(table_name) over (order by table_name asc rows between unbounded preceding and unbounded following) last_name

Alternatively, you could get the very last and very first whole rows by changing the outer where clause:

   ord=1 or rownum=1 or ord between 10 and 19

And then it would be easy to have a nice header with "rows 10 - 19 of 81 (Algiers to Zimbabwe)" with very little code.

[bmn__]

If you mean pagination, then window functions are not the right tool. Keyset pagination in standard SQL:

• https://use-the-index-luke.com/sql/partial-results/fetch-nex...

• https://use-the-index-luke.com/no-offset

[e12e]

Both of these are bad for pagination - if the dataset isn't read-only. If the search criteria matches 100 rows at the time of the request, you may want to page through those matches, even if, by the time the client (human or machine) gets to page three, the query matches 80 or 110 rows - or worse, if the query still matches 100 rows, but not all of them are the same as the original 100!

You would normally capture such state by using cursors.

[hospadar]

row_number() !

As in: SELECT * FROM (SELECT *, row_number() OVER (order by <some column - primary key column would be a good choice>) as rowidx FROM your_table) as numbered WHERE rowidx > ? AND rowid < ?