I am writing a query that needs to be able to return several fields, however for the fields from my [Opportunity] table i.e. [stage] & [yearid], it needs to be static relative to the max year [yearid] for that respective school. I have drafted up a high-level example of the schema for the tables that I am using and have provided it in the sql fiddle below. Referencing the data provided, I essentially would want a single line item for each school and have all values referenced from the [Opportunity] table be pulled from the max [yearid]. In all of these instances, I would expect to see:
| schoolname | yearid | stage |
|---|---|---|
| schoola | 10015 | Close |
| schoolb | 10015 | Close |
| schoolc | 10015 | Close |
The issue that I am running into is that when I try to grab the Opportunity values based on the max yearid, I am still returning all results. I have made several attempts to try to resolve my issue such as using subqueries in the SELECT statement, using SUBJOINS, using ORDER BY yearid DESC OFFSET 0 ROWS etc. however I am not able to resolve this of my own accord. It is very feasible that one of the solutions that I have attempted would work, however I am simply not structuring my query properly.
http://sqlfiddle.com/#!9/92a53a/1
CREATE TABLE account
(accountid INT NOT NULL
, accschoolname varchar(20) NOT NULL
, PRIMARY KEY (accountid)
) ;
INSERT INTO account VALUES (1001, 'schoola') ;
INSERT INTO account VALUES (1002, 'schoolb' ) ;
INSERT INTO account VALUES (1003, 'schoolc' ) ;
CREATE TABLE opportunity
(oppid INT NOT NULL
, yearid int NOT NULL
, stage varchar(20) NOT NULL
, accountid int NOT NULL
, oppschoolname varchar(20) NOT NULL
, PRIMARY KEY (oppid)
) ;
INSERT INTO opportunity VALUES (1, 10013, 'Sent', 1001, 'schoola2021') ;
INSERT INTO opportunity VALUES (2, 10014, 'Run', 1001, 'schoola2022') ;
INSERT INTO opportunity VALUES (3, 10015, 'Close', 1001, 'schoola2023');
INSERT INTO opportunity VALUES (4, 10013, 'Sent', 1002, 'schoolb2021') ;
INSERT INTO opportunity VALUES (5, 10014, 'Run', 1002, 'schoolb2022') ;
INSERT INTO opportunity VALUES (6, 10015, 'Close', 1002, 'schoolb2023');
INSERT INTO opportunity VALUES (7, 10013, 'Sent', 1003, 'schoolc2021') ;
INSERT INTO opportunity VALUES (8, 10014, 'Run', 1003, 'schoolc2022') ;
INSERT INTO opportunity VALUES (9, 10015, 'Close', 1003, 'schoolc2023');
CREATE TABLE contact
(contactid INT NOT NULL
, oppid INT NOT NULL
, firstname varchar(20)
, lastname varchar(20)
, email varchar(20)
, PRIMARY KEY (contactid)
) ;
select a.accschoolname, max(yearid) as yearid, o.stage
from account a
join opportunity o on o.accountid = a.accountid
group by a.accschoolname, o.stage
EDIT: My apologies for failing to mention that this query is being built for a user who will replicate it in Salesforce Marketing Cloud's native query builder. The issue is, I am unsure if CTE is used within SFMC so I do not believe the answer provided in the related post will solve my issue.
