Re: Window Functions

On Tue, 2008-10-14 at 11:05 +0200, Andreas Joseph Krogh wrote:
> Hi all.
> This is not very "hackers"-related, but related to the topic of window-funcitons, which seems to be discussed quite a
biton "hackers" these days.
 
> 
> Can window-functions in PG be used to return "total number of rows" in a "paged result"?
> Say you have:
> SELECT p.id, p.firstname
>   FROM person p
>  ORDER BY p.firstname ASC
>  LIMIT 10 OFFSET 10
> 
> Is it possible to use some window-function to return the "total-number of columns" in a separate column?
> 
> In Oracle one can do 
> SELECT q.*, max(rownum) over() as total_rows FROM (subquery)
> which returns the total number or columns in a separate column. This is very handy for web-pages which for example
needto display the rist 20 results of several million, without having to do a separate count(*) query.
 

no need to use window functions here, just ask for max inline:


hannu=# select rownum, word, (select max(rownum) from words) as maxrow
from words limit 10;rownum |   word    | maxrow 
--------+-----------+--------     1 |           |  98569     2 | A         |  98569     3 | A's       |  98569     4 |
AOL      |  98569     5 | AOL's     |  98569     6 | Aachen    |  98569     7 | Aachen's  |  98569     8 | Aaliyah   |
98569    9 | Aaliyah's |  98569    10 | Aaron     |  98569
 
(10 rows)


---------------------
Hannu Krosing