From: | Hannu Krosing <hannu(at)2ndQuadrant(dot)com> |
---|---|
To: | Andreas Joseph Krogh <andreak(at)officenet(dot)no> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Window Functions |
Date: | 2008-10-14 16:19:07 |
Message-ID: | 1224001147.8222.5.camel@huvostro |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 bit on "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 need to 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
From | Date | Subject | |
---|---|---|---|
Next Message | Zhe He | 2008-10-14 16:19:17 | Problem to get the tuple of a table |
Previous Message | Peter Eisentraut | 2008-10-14 16:18:16 | Re: CLUSTER, REINDEX, VACUUM in "read only" transaction? |