SELECT 
CASE WHEN (RX.STORENBR = '101114') THEN 'CC-FTL'  
WHEN (RX.STORENBR = '103073') THEN 'CC-MIA'  
WHEN (RX.STORENBR = '102520') THEN 'CC-WPB'  
ELSE '0' 
END AS PHARMACY_NAME, 
CASE WHEN (RX.STORENBR = '101114') THEN '1598762015'  
WHEN (RX.STORENBR = '103073') THEN '1063423606'  
WHEN (RX.STORENBR = '102520') THEN '1770580292'  
ELSE '0' 
END AS PHARMACY_NPI,
CASE WHEN (RX.STORENBR = '101114') THEN '1079638'  
WHEN (RX.STORENBR = '103073') THEN '1019303'  
WHEN (RX.STORENBR = '102520') THEN '1099286'  
ELSE '0' 
END AS PHARMACY_NCPDP,
CASE WHEN (RX.STORENBR = '101114') THEN 'BN9568861'  
WHEN (RX.STORENBR = '103073') THEN 'BN9754450'  
WHEN (RX.STORENBR = '102520') THEN 'BA8399962'  
ELSE '0' 
END AS PHARMACY_DEA,
PATIENT.PATSTOREID AS PT_STORE, 
PATIENT.PATKEY AS PT_KEY, 
(PATIENT.PATSTOREID || LPAD(PATIENT.PATKEY, 6, 0)) AS PTID, 
DOCTOR.NAMELAST AS PRESCRIBER_LAST, 
DOCTOR.NAMEFIRST AS PRESCRIBER_FIRST, 
DOCTOR.ADRSCITY AS PRESCRIBER_CITY, 
DOCTOR.ADRSSTATE AS PRESCRIBER_STATE, 
DOCTOR.ADRSZIP AS PRESCRIBER_ZIP, 
DOCTOR.STATENUMBER,
DOCTOR.DEAALPHAALPHA || DOCTOR.DEAALPHANBR || DOCTOR.DEAALPHASFX AS PRESCRIBER_DEA,
CASE WHEN (DOCTOR.PLANCODE01 = 'NPI') THEN DOCTOR.PLANID01 
WHEN (DOCTOR.PLANCODE02 = 'NPI') THEN DOCTOR.PLANID02 
WHEN (DOCTOR.PLANCODE03 = 'NPI') THEN DOCTOR.PLANID03 
ELSE '0' 
END AS NPI, 
DOCTOR.SPECIALTY,    
DRUG.NAME, DRUG.DRUGNDCNBR,
PLANS.PLANID, 
(NVL(RX.CASHPAID, 0)/100) AS COPAY, 
to_char(to_date(RX.FILLDATE, 'yyyyddd'), 'MM/DD/YYYY') AS DISPENSED_DATE,
RX.DISPENSEDQTY01, RX.DAYSUPP,  
FROM RX 
LEFT OUTER JOIN PLANS ON (RX.PLANSTOREID01=PLANS.PLANCODESTOREID AND RX.PLANKEY01=PLANS.PLANCODEKEY)  
LEFT OUTER JOIN DRUG ON (RX.DISPENSEDDRUGSTOREID=DRUG.DRUGNBRSTOREID AND RX.DISPENSEDDRUGKEY=DRUG.DRUGNBRKEY) 
LEFT OUTER JOIN DOCTOR ON (RX.DOCNBRSTOREID=DOCTOR.DOCSTOREID AND RX.DOCNBRKEY = DOCTOR.DOCKEY) 
LEFT OUTER JOIN PATIENT ON (RX.PATNBRSTOREID=PATIENT.PATSTOREID AND RX.PATNBRKEY=PATIENT.PATKEY) 
WHERE RX.FILLDATE >= 2007274 
AND RX.FILLDATE <= 2007304 
AND DRUG.DRUGNDCNBR = 0004038039 
AND NVL(RX.RXSTATUS, 0) <> 16 
AND NVL(RX.RXSTATUS, 0) <> 17 
AND NVL(RX.RXSTATUS, 0) <> 18 
AND NVL(RX.RXSTATUS, 0) <> 20 
AND NVL(RX.RXSTATUS, 0) <> 24 
AND NVL(RX.RXSTATUS, 0) <> 32 
AND NVL(RX.RXSTATUS, 0) <> 33 
AND NVL(RX.RXSTATUS, 0) <> 48 

(
  SELECT * FROM (
                  SELECT RX.FILLDATE AS FIRST_DATE 
                  FROM RX
                  LEFT OUTER JOIN DRUG ON (RX.DISPENSEDDRUGSTOREID=DRUG.DRUGNBRSTOREID AND RX.DISPENSEDDRUGKEY=DRUG.DRUGNBRKEY)
                  WHERE RX.PATNBRSTOREID=PT_STORE  
                  AND RX.PATNBRKEY=PT_KEY  
                  AND DRUG.DRUGNDCNBR = 0004038039  
                  ORDER BY LEAST(RX.FILLDATE) 
                )
  WHERE ROWNUM = 1
)

MIN(RX.FILLDATE) OVER (partition by RX.PATNBRSTOREID, RX.PATNBRKEY) earliest_fill_date

SQL> select ename, hiredate, 
  2         min(hiredate) over(partition by deptno) "First hire in dept"
  3  from emp
  4  order by ename
  5  /
ENAME      HIREDATE    First hire
---------- ----------- -----------
ADAMS      23-MAY-1987 17-DEC-1980
ALLEN      20-FEB-1981 20-FEB-1981
BLAKE      01-MAY-1981 20-FEB-1981
CLARK      09-JUN-1981 09-JUN-1981
FORD       03-DEC-1981 17-DEC-1980
JAMES      03-DEC-1981 20-FEB-1981
JONES      02-APR-1981 17-DEC-1980
KING       17-NOV-1981 09-JUN-1981
MARTIN     28-SEP-1981 20-FEB-1981
MILLER     23-JAN-1982 09-JUN-1981
SCOTT      19-APR-1987 17-DEC-1980
SMITH      17-DEC-1980 17-DEC-1980
TURNER     08-SEP-1981 20-FEB-1981
WARD       22-FEB-1981 20-FEB-1981

14 rows selected.

SQL> with data as (
  2    select ename, hiredate, 
  3           min(hiredate) over(partition by deptno) "First hire in dept"
  4    from emp
  5  )
  6  select * from data where hiredate >= to_date('1983','YYYY')
  7  order by ename
  8  /
ENAME      HIREDATE    First hire
---------- ----------- -----------
ADAMS      23-MAY-1987 17-DEC-1980
SCOTT      19-APR-1987 17-DEC-1980

2 rows selected.