[forinti]

A few cool tricks I use with window functions:

1- To find blocks of contiguous values, you can use something similar to Gauss' trick for calculating arithmetic progressions: sort them by descending order and add each value to the row number. All contiguous values will add to the same number. You can then apply max/min and get rows that correspond to the blocks of values.

    select min(n), max(n) from (
      select n, n+row_number() over (order by n desc) group
      from numbers
    )
    group by group
    order by 1

2- You can use a window function with exponential/logarithms in order to calculate the accumulated inflation for the last n months:

    select date, inflation, (exp(accumulated)-1)*100 from (
      select date, inflation, sum(ln(1+(inflation/100))) over (order by date desc rows between current row and 11 following) as accumulated
      from inflation
    ) 

3- You can do all the paging in SQL (fetch page n of m) or simply add a column with the total number of rows (this often makes it easier to process the results).

[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.

[fuy]

Could you elaborate on "using cursors" part? Are you talking about database-level cursors? If so, do you know any resources which cover this approach?

[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 < ?