Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Exercise (
SubscriberID int,
StatementDate DATE,
AcquisitionFactor DECIMAL(4,2));
insert into Exercise values (1, DATE '2005-03-15', 80.0);
1 rows affected

insert into Exercise values (1, DATE '2005-04-15', 0.90);
1 rows affected

insert into Exercise values (1, DATE '2005-05-15', 0.90);
1 rows affected

insert into Exercise values (1, DATE '2005-06-15', 0.90);
1 rows affected

insert into Exercise values (2, DATE '2017-08-15', 66.7);
1 rows affected

insert into Exercise values (2, DATE '2017-09-15', 66.7);
1 rows affected

insert into Exercise values (2, DATE '2017-10-15', 0.50);
1 rows affected

insert into Exercise values (3, DATE '2017-10-15', 0.66);
1 rows affected

insert into Exercise values (4, DATE '2017-10-15', 0.55);
1 rows affected

SELECT SubscriberID, AcquisitionFactor FROM Exercise WHERE StatementDate=(SELECT max(StatementDate) FROM Exercise);


SUBSCRIBERID
ACQUISITIONFACTOR


2
.5

3
.66

4
.55


SELECT SubscriberID,
  MAX(AcquisitionFactor) KEEP (DENSE_RANK LAST ORDER BY StatementDate) AS AcquisitionFactor
FROM Exercise
GROUP BY SubscriberID;


SUBSCRIBERID
ACQUISITIONFACTOR


1
.9

2
.5

3
.66

4
.55


SELECT SubscriberID,
  MAX(StatementDate) AS StatementDate,
  MAX(AcquisitionFactor) KEEP (DENSE_RANK LAST ORDER BY StatementDate) AS AcquisitionFactor
FROM Exercise
GROUP BY SubscriberID;


SUBSCRIBERID
STATEMENTDATE
ACQUISITIONFACTOR


1
15-JUN-05
.9

2
15-OCT-17
.5

3
15-OCT-17
.66

4
15-OCT-17
.55