This query return the right results for an individual pidm, (SPRIDEN_PIDM = 42306) but not when I run it for all the pidms in the table
I get something like this which is fine,
| 42306 | Z00717850 | 2 |
| 42306 | Z00630015 | 1 |
| 42306 | Z00822647 | 3 |
I want to be able to comment it out the (SPRIDEN_PIDM = 42306) and got the same results for each pidm
like
| 42306 | Z00717850 | 2 |
| 42306 | Z00630015 | 1 |
| 42306 | Z00822647 | 3 |
| 42807 | Z00717850 | 2 |
| 42807 | Z00630015 | 1 |
| 42807 | Z00822647 | 3 |
etc..etc.. it seems that is not grouping correctly
SELECT
SPRIDEN_PIDM,
SPRIDEN_ID,
ROWNUM
FROM (SELECT SPRIDEN_PIDM,
SPRIDEN_ID,
ROW_NUMBER ()
OVER (PARTITION BY sPRIDEN_pidm ORDER BY SPRIDEN_ID DESC) --- test_date DESC)
rn
FROM (SELECT SPRIDEN_PIDM,
SPRIDEN_ID
FROM SPRIDEN
WHERE SPRIDEN_ID LIKE 'Z%'
AND SPRIDEN_PIDM = 42306
GROUP BY
SPRIDEN_PIDM ,
SPRIDEN_ID ))
GROUP BY SPRIDEN_pidm,SPRIDEN_ID,ROWNUM
having ROWNUM >= 1