title: PostgreSQL: using row_number() in paged output
body: <p>I have a PHP script displaying a list of players sorted by their "virtual money":</p>
  
  <pre><code>$sth = $db-&gt;prepare("
  select u.id,
          u.first_name,
          u.city,
          u.avatar,
          m.money,
          u.login &gt; 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-&gt;execute(array($_GET['offset']));
  </code></pre>
  
  <p>To show a player position in the list I use a PHP variable $pos which is incremented in a loop while printing their names and further data.</p>
  
  <p>I would like to have that position in the SQL statement instead of PHP for various reasons. So I'm trying the following:</p>
  
  <pre><code>$sth = $db-&gt;prepare("
  select u.id,
          row_number() + ? as pos,
          u.first_name,
          u.city,
          u.avatar,
          m.money,
          u.login &gt; 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-&gt;execute(array($_GET['offset'], $_GET['offset']));
  </code></pre>
  
  <p>But get the <strong>ERROR:  window function call requires an OVER clause</strong></p>
  
  <p>I'm trying to add <strong>over(m.money)</strong> but get a syntax error.</p>
  
  <p>I'm probably misunderstanding the <a href="http://www.postgresql.org/docs/8.4/interactive/tutorial-window.html" rel="nofollow">Window Functions</a> doc.</p>