Restart Record Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I am counting records in a query and I need the count to restart at 1
for each sub-set. In the example below, I want to count [Action ID] within
each set of [ISSUE ID] and result in [New Count]. Any suggestions are very
much appreciated.
ISSUE ID Action ID New Count
123 95 1
123 97 2
123 98 3
123 101 4
456 110 1
456 116 2
456 118 3

Thanks so much!!
 
the general query (for display purposes) is
SELECT (SELECT COUNT(*)+1 FROM P P1 WHERE P1.ID < P.ID) AS ROWNUM, P.*
FROM P P;

so you'd want
SELECT (SELECT COUNT(*)+1 FROM yourtablename P1 WHERE P1.ISSUEID = P.ISSUEID
AND PI.ACTIONID < P.ACCTIONID) AS ROWNUM, P.*
FROM yourtablename P;
 
Back
Top