title: rownum in Oracle sql with group by
body: <p>I need to build a query to retrieve information group by Members and an expiration Date but I need to have a sequence number for every Member..</p>
  
  <p>So for example:</p>
  
  <p>If Member "A" has 3 records to expire, "B" has only 1 and "C" has 2, I need a result like this:</p>
  
  <pre>
  Number    Member  ExpDate
   1           A    01/01/2020
   2           A    02/01/2020
   3           A    03/01/2020
   1           B    01/01/2020
   1           C    01/01/2020
   2           C    02/01/2020
  </pre>
  
  <p>My query now is:</p>
  
  <pre><code>SELECT ROW_NUMBER() OVER(ORDER BY TRUNC(EXPIRATION_DT) ASC) AS SEQUENCE, MEMBER_ID AS MEMBER, SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) AS POINTS, trunc(EXPIRATION_DT) AS EXPDATE
  FROM TABLE1
  WHERE EXPIRATION_DT &gt; SYSDATE AND  EXPIRATION_DT &lt; SYSDATE + 90
  GROUP BY MEMBER_ID, TRUNC(EXPIRATION_DT)
  HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) &gt; 0
  ORDER BY 4 ASC;
  </code></pre>
  
  <p>But I cant' "group" the sequence number.... The result now is:</p>
  
  <pre>
  Seq Mem Points Date
  1   1-O  188   2018-03-01 00:00:00
  2   1-C  472   2018-03-01 00:00:00
  3   1-A  485   2018-03-01 00:00:00
  4   1-1  267   2018-03-01 00:00:00
  5   1-E  500   2018-03-01 00:00:00
  6   1-P  55    2018-03-01 00:00:00
  7   1-E  14    2018-03-01 00:00:00
  </pre>