4 ms·

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 solut

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.

hospadar 6y ago
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 < ?
forinti 6y ago
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__ 6y ago
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-next-page https://use-the-index-luke.com/sql/partial-results/fetch-nex... • https://use-the-index-luke.com/no-offset https://use-the-index-luke.com/no-offset
- e12e 6y ago
  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 6y ago
    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 6y ago

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

forinti 6y ago

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__ 6y ago

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-next-page https://use-the-index-luke.com/sql/partial-results/fetch-nex... • https://use-the-index-luke.com/no-offset https://use-the-index-luke.com/no-offset

e12e 6y ago
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 6y ago
  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?