PostgreSQL: using row_number () in paged output

Question

I have a PHP script displaying a list of players sorted by their "virtual money":

$sth = $db->prepare("
select u.id,
        u.first_name,
        u.city,
        u.avatar,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 20 offset ?
");
$sth->execute(array($_GET['offset']));

To show the player’s position in the list, I use the PHP variable $ pos, which increases in the loop when printing their names and additional data.

I would like to have this position in the SQL expression instead of PHP for various reasons. So I am trying to do the following:

$sth = $db->prepare("
select u.id,
        row_number() + ? as pos,
        u.first_name,
        u.city,
        u.avatar,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 20 offset ?
");
$sth->execute(array($_GET['offset'], $_GET['offset']));

But getting the ERROR call : calling a window function requires an OVER clause

I try to add over (m.money) , but I get a syntax error.

Perhaps I misunderstand the functions of the doc window .

Answer 1

: http://www.postgresql.org/docs/8.4/interactive/functions-window.html

, Over() order by :

$sth = $db->prepare("
select u.id,
        row_number() OVER (order by m.money desc) + ? as pos,
        u.first_name,
        u.city,
        u.avatar,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 20 offset ?
");
$sth->execute(array($_GET['offset'], $_GET['offset']));

Answer 2

row_number() OVER (ORDER BY m.money) + ? ..