title: Window Function in PostgreSQL
body: <p>I have a  with my SELECT statement that I just can't figure out. The query is as follows:</p>
  
  <pre><code>SELECT 
    count(1),
    interaction_type_id 
  FROM
    tibrptsassure.d_interaction_sub_type
  GROUP BY 
    interaction_type_id
  HAVING 
    count(interaction_type_id) &gt; 1 
  ORDER BY
   count(interaction_type_id) DESC 
  LIMIT 5;
  </code></pre>
  
  <p>Since my application does not support the use of the LIMIT keyword, I tried changing my query using the <code>rank()</code> function like so:</p>
  
  <pre><code>SELECT
   interaction_type_id, 
   rank() OVER (PARTITION BY interaction_type_id ORDER BY count(interaction_type_id)
   DESC) 
  FROM 
   tibrptsassure.d_interaction_sub_type;
  </code></pre>
  
  <p>However, this way I ended up with the following error message:</p>
  
  <pre><code>ERROR:  column "d_interaction_sub_type.interaction_type_id" must appear in the GROUP BY clause or be used in an aggregate function
  LINE 1: SELECT  interaction_type_id, rank() OVER (PARTITION BY inter...    
                      ^
      ********** Error **********
  
  ERROR: column "d_interaction_sub_type.interaction_type_id" must appear in the GROUP BY clause or be used in an aggregate function
  SQL state: 42803
  Character: 9
  </code></pre>
  
  <p>Is there an equivalent of <code>rownum()</code> in PostgreSQL? (Apart from using the LIMIT keyword to achieve the same result, that is.)</p>
  
  <p>Does anybody have any suggestions for me? Thanks in advance.</p>