rownum in Oracle sql with group by

Question

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..

So for example:

If Member "A" has 3 records to expire, "B" has only 1 and "C" has 2, I need a result like this:

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

My query now is:

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 > SYSDATE AND  EXPIRATION_DT < SYSDATE + 90
GROUP BY MEMBER_ID, TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0
ORDER BY 4 ASC;

But I cant' "group" the sequence number.... The result now is:

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

Solution 1

I think you need a DENSE_RANK window function. try this -

 SELECT DENSE_RANK() OVER (PARTITION BY MEMBER 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 > SYSDATE AND  EXPIRATION_DT < SYSDATE + 90
GROUP BY MEMBER_ID
        ,TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0
ORDER BY 4 ASC;

Solution 2

with g as (
select *
From TABLE1 g
group by MEMBER_ID
        ,TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0   ---- etc
) 
select rownum, g.* From g

this select return first column with sequence number

Comments

[Dmitriy]

Could you please explain, what is the column Mem in your actual result and how do you convert it into "A", "B", "C"?

[LPS]

A,B or C were just examples of possible values...