How to get WHERE statement to return a result for each unique ID?-CodePudding

I need to get the most recent ACQUISITIONFACTOR for each subscriber ID from the following table:

CREATE TABLE Exercise 
(
    SubscriberID int,
    StatementDate DATE,
    AcquisitionFactor DECIMAL(4, 2)
);

INSERT INTO Exercise VALUES (1, DATE '2005-03-15', 80.0);
INSERT INTO Exercise VALUES (1, DATE '2005-04-15', 0.90);
INSERT INTO Exercise VALUES (1, DATE '2005-05-15', 0.90);
INSERT INTO Exercise VALUES (1, DATE '2005-06-15', 0.90);
INSERT INTO Exercise VALUES (2, DATE '2017-08-15', 66.7);
INSERT INTO Exercise VALUES (2, DATE '2017-09-15', 66.7);
INSERT INTO Exercise VALUES (2, DATE '2017-10-15', 0.50);
INSERT INTO Exercise VALUES (3, DATE '2017-10-15', 0.66);
INSERT INTO Exercise VALUES (4, DATE '2017-10-15', 0.55);

SUBSCRIBERID	STATEMENTDATE	ACQUISITIONFACTOR
1	15-MAR-05	80
1	15-APR-05	.9
1	15-MAY-05	.9
1	15-JUN-05	.9
2	15-AUG-17	66.7
2	15-SEP-17	66.7
2	15-OCT-17	.5
3	15-OCT-17	.66
4	15-OCT-17	.55

This is the query that I've come up with.

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

SUBSCRIBERID	ACQUISITIONFACTOR
2	.5
3	.66
4	.55

The problem is that it only returns results from SUBSCRIBERID 2 - 4. How do I get it to return the AcquisitionFactor for all 4 IDs? Thanks!

CodePudding user response：

You can use the aggregate max function with the last function:

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

db<>fiddle

It's trivial to then include the date as well if you want that - included as a final query in that fiddle.

CodePudding user response：

You are close with your solution, but you need to correlate that subquery by providing a relationship between your main query and your subquery in your subquery's WHERE clause.

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