By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE PER_PERSON_ADDR_USAGES_F (PERSON_ID, ADDR_ID, TYPE, EFF_START_DATE, EFF_END_DATE) AS
SELECT 6207445, '7390814', 'HOME', DATE '2024-02-26', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '0042548', 'HOME', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '6462255', 'HOME', DATE '2022-01-31', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '6462287', 'MAIL', DATE '2022-01-31', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 6207445, '0042571', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL UNION ALL
SELECT 1111111, '0042578', 'MAIL', DATE '2023-05-15', DATE '4712-12-31' FROM DUAL;
6 rows affected
SELECT person_id,
type,
COUNT(*) AS rec_count
FROM PER_PERSON_ADDR_USAGES_F
GROUP BY person_id, type
ORDER BY rec_count DESC
| PERSON_ID | TYPE | REC_COUNT |
|---|---|---|
| 6207445 | HOME | 3 |
| 6207445 | 2 | |
| 1111111 | 1 |
SELECT person_id,
type,
MAX(addr_id) KEEP (DENSE_RANK LAST ORDER BY eff_start_date) AS addr_id,
COUNT(*) AS rec_count
FROM PER_PERSON_ADDR_USAGES_F
GROUP BY person_id, type
ORDER BY rec_count DESC
| PERSON_ID | TYPE | ADDR_ID | REC_COUNT |
|---|---|---|---|
| 6207445 | HOME | 7390814 | 3 |
| 6207445 | 0042571 | 2 | |
| 1111111 | 0042578 | 1 |
SELECT person_id,
type,
addr_id,
rec_count
FROM (
SELECT person_id,
type,
addr_id,
ROW_NUMBER() OVER (PARTITION BY person_id, type ORDER BY eff_start_date DESC)
AS rn,
COUNT(*) OVER (PARTITION BY person_id, type) AS rec_count
FROM PER_PERSON_ADDR_USAGES_F
)
WHERE rn = 1
ORDER BY rec_count DESC
| PERSON_ID | TYPE | ADDR_ID | REC_COUNT |
|---|---|---|---|
| 6207445 | HOME | 7390814 | 3 |
| 6207445 | 0042571 | 2 | |
| 1111111 | 0042578 | 1 |