Optimal way to pull the most recent goal value for each unique goal with this table structure?

Question

So I have a table structured similarly to this:

GoalID	GoalName	GoalType	UsedTimeframe	ChangedTimeframe	GoalUpperBound	GoalLowerBound	GoalValue	EffectiveDate	EndDate
1	Sales	Single Target	Annually	Annually	NULL	NULL	5,000,000	01-01-2021	12-31-2021
2	Unique Purchasers	Range	Monthly	Monthly	22,000	20,000	NULL	9-01-2021	9-30-2021
3	Sales	Single Target	Monthly	Annually	NULL	NULL	500,000	10-01-2021	10-31-2021
4	Unique Purchasers	Range	Monthly	Monthly	24,000	21,000	NULL	10-01-2021	10-31-2021
5	New Customers	Single Target	Monthly	Annually	NULL	NULL	5,000	01-01-2021	12-31-2021
6	Products Sold	Range	Monthly	Monthly	10,000	9,000	NULL	10-01-2021	10-31-2021
7	Products Sold	Range	Monthly	Monthly	12,000	10,000	NULL	10-15-2021	10-31-2021
8	Sales	Single Target	Annually	Annually	NULL	NULL	4,750,000	01-01-2020	12-31-2020
9	Referrals	Single Target	Monthly	Annually	NULL	NULL	1,000	01-01-2021	12-31-2021

What is the most optimal way to pull each unique GoalName and their corresponding GoalUpperBound/GoalLowerBound or GoalValue? So in this example, if I wanted to pull all goals that change Monthly, I'd want to see GoalIDs (3, 4, 5, 7, 9) and if I was pulling goals that change Annually, I'd see GoalID (1). I was thinking pulling the MAX(GoalID) for each DISTINCT GoalName and UsedTimeframe and then joining back the GoalID to this table or maybe the MAX(EffectiveDate)?

Yes. You seem to want to do something informally called "groupwise max", where you wish to obtain the most recent row per `x` group based on some `y` ordering. Did you always want the GoalID associated with the `MAX(EffectiveDate)` for each `GoalName`? It's usually best not to depend on the GoalID values unless you know that order satisfies your requirement always. Get the logic right first, then work on performance as a separate step. — Jon Armstrong, Oct 17 '21 at 21:44
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Oct 17 '21 at 21:48
To address performance, you'll want to provide more detail, like the `CREATE TABLE` statement, including all constraints / indexes, plus some information about the size / range of the data involved. Your database will often be able to help by showing the `explain plan` for various test cases with sufficient data. — Jon Armstrong, Oct 17 '21 at 21:51
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Teasing us with a structure that is _like_ your table and where every column is `NVarChar(MAX)` doesn't help us help you. Providing consumable sample data, expected results and your code does help. So does explaining the data, e.g. how does `UsedTimeframe` affect the expected results? — HABO, Oct 17 '21 at 22:17

score 0 · Answer 1 · answered Oct 18 '21 at 13:32

You can use the windowing functions, specifically ROW_NUMBER() to number the groups my GoalID DESC - then you just want the groups with a ROW_NUMBER() = 1. Since you can't use windowing functions in a WHERE clause, you have to write it as a subquery.

To test this out, I created a table variable and put your data into it:

DECLARE @Goals AS TABLE (
    GoalID INT NOT NULL, 
    GoalName VARCHAR(20) NOT NULL, 
    GoalType VARCHAR(20) NOT NULL, 
    UsedTimeframe VARCHAR(20) NOT NULL, 
    ChangedTimeframe VARCHAR(20) NOT NULL,
    GoalUpperBound INT NULL,
    GoalLowerBound INT NULL,
    GoalValue INT NULL,
    EffectiveDate DATE NOT NULL,
    EndDate DATE NOT NULL
);

INSERT INTO @Goals(GoalID,GoalName,GoalType,UsedTimeframe,ChangedTimeframe,GoalUpperBound,GoalLowerBound,GoalValue,EffectiveDate,EndDate)
VALUES 
(1,'Sales','Single Target','Annually','Annually',NULL,NULL,5000000,'01-01-2021','12-31-2021'),
(2,'Unique Purchasers','Range','Monthly','Monthly',22000,20000,NULL,'9-01-2021','9-30-2021'),
(3,'Sales','Single Target','Monthly','Annually',NULL,NULL,500000,'10-01-2021','10-31-2021'),
(4,'Unique Purchasers','Range','Monthly','Monthly',24000,21000,NULL,'10-01-2021','10-31-2021'),
(5,'New Customers','Single Target','Monthly','Annually',NULL,NULL,5000,'01-01-2021','12-31-2021'),
(6,'Products Sold','Range','Monthly','Monthly',10000,9000,NULL,'10-01-2021','10-31-2021'),
(7,'Products Sold','Range','Monthly','Monthly',12000,10000,NULL,'10-15-2021','10-31-2021'),
(8,'Sales','Single Target','Annually','Annually',NULL,NULL,4750000,'01-01-2020','12-31-2020'),
(9,'Referrals','Single Target','Monthly','Annually',NULL,NULL,1000,'01-01-2021','12-31-2021');

The query is then:

SELECT GoalID,GoalName,GoalType,UsedTimeframe,ChangedTimeframe,GoalUpperBound,GoalLowerBound,GoalValue,EffectiveDate,EndDate 
FROM 
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY GoalName ORDER BY GoalID DESC) AS rn
    FROM @Goals 
    WHERE UsedTimeframe = 'Monthly' 
) AS orderedSubQuery 
WHERE rn = 1 
ORDER BY GoalID

This returns Goals 3, 4, 5, 7, 9 as you requested.

Optimal way to pull the most recent goal value for each unique goal with this table structure?

1 Answers1