Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ALBUMS AS
SELECT 'Band1' AS BAND, TO_DATE('12/12/68', 'MM/DD/RR') AS RELEASE_DATE FROM DUAL UNION ALL
SELECT 'Band1', TO_DATE('08/05/75', 'MM/DD/RR')  FROM DUAL UNION ALL
SELECT 'Band2', TO_DATE('09/02/75', 'MM/DD/RR')  FROM DUAL UNION ALL
SELECT 'Band2', TO_DATE('05/05/99', 'MM/DD/RR')  FROM DUAL UNION ALL
SELECT 'Band2', TO_DATE('03/05/01', 'MM/DD/RR')  FROM DUAL
5 rows affected

SELECT * FROM ALBUMS


BAND
RELEASE_DATE


Band1
12-DEC-68

Band1
05-AUG-75

Band2
02-SEP-75

Band2
05-MAY-99

Band2
05-MAR-01


WITH cteBand_activity AS
        (SELECT BAND,
                MIN(RELEASE_DATE) AS FIRST_RELEASE_DATE,
                MAX(RELEASE_DATE) AS LATEST_RELEASE_DATE,
                TRUNC((MAX(RELEASE_DATE) - MIN(RELEASE_DATE)) / 365) + 1 AS YEARS_ACTIVE
           FROM ALBUMS
           GROUP BY BAND
           ORDER BY YEARS_ACTIVE DESC)
SELECT BAND, YEARS_ACTIVE
  FROM cteBand_activity
  WHERE ROWNUM = 1
  


BAND
YEARS_ACTIVE


Band2
26

BAND	RELEASE_DATE
Band1	12-DEC-68
Band1	05-AUG-75
Band2	02-SEP-75
Band2	05-MAY-99
Band2	05-MAR-01