Inner Join with Data view and data extension in SFMC not working

Question

I am trying to do a inner join with one data extension and the data view _sent, inner join on subscriber key from _sent and contactid from data extension. I want to fetch the max event date i.e the last sent date from _sent data view. But whenever I am using the max aggregate function, its throwing an error as "An error occurred while checking the query syntax. Errors: Column 'C6300865.Contact_BrandPref_CCV.createdon' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." It is working without the max function.

Can anyone help what wrong I am doing in this below query:

SELECT
createdon,
firstname,
lastname,
hsl_forecordid,
address1_telephone2,
address1_telephone1,
hsl_customertype,
birthdate,
hsl_dealseeker,
hsl_employee,
hsl_formertweemployee,
hsl_giftmember,
hsl_id18salesforcelegacy,
hsl_leadsource,
hsl_local,
hsl_longestmembership,
hsl_mandarin,
hsl_nameofpartnersignificantother,
hsl_napaneighbor,
hsl_nationaldonotcall,
hsl_phoneunsubscribe,
hsl_tweboardmember,
middlename,
msdyn_gdproptout,
new_globaloptout,
nickname,
modifiedon,
hsl_bouncedate,
hsl_bouncedemail,
hsl_bouncedemailreason,
hsl_lastclickdate,
hsl_lastopendate,
hsl_lastsentdate,
hsl_retiredfrommarketingcloud,
emailaddress1,
hsl_listofpurchasedvarieties,
hsl_priorprimaryemail,
hsl_totalbottleslastyear,
hsl_lastorderdate,
hsl_totalbottles,
hsl_highestbottleprice,
hsl_clubmembership,
hsl_countoforders,
hsl_activeclubmemberships,
hsl_paidforwinelastyr,
hsl_paidforwineytd,
hsl_clubtotaltenure,
hsl_numberoforderslastyear,
hsl_totalpaidforwine,
hsl_highestordervalue,
hsl_totalordervaluelastyear,
hsl_totalordervalue,
hsl_totalofbottleslastyear,
hsl_totalpaidforcorporategifting,
contactid,
displayrow,
hsl_donotallowemails,
hsl_donotallowmails,
hsl_donotallowphonecalls,
hsl_donotallowsms,
hsl_donotcallmobile,
hsl_legacyid,
hsl_brandpreferenceid,
hsl_mccampaignunsubscribedfrom,
hsl_name,
hsl_leadsourcename,
hsl_brandname,
hsl_mcpreferredemailfrequency,
statecode,
hsl_winerytrade,

MAX(sent.[eventdate]) AS [SendDate],
sent.[subscriberkey] AS [subscriberkey]

FROM Ent.Contact_BrandPref_CCV 
INNER JOIN _sent AS sent 
ON [contactid] = sent.[subscriberkey]

WHERE DATEPART(month, DATEADD(day, 0, birthdate)) = DATEPART(month, GETDATE())
AND DATEPART(day, DATEADD(day, 0, birthdate)) = DATEPART(day, GETDATE())
AND hsl_donotallowemails = 'false'

AND (
    DATEDIFF(DAY, sent.eventdate, GETDATE()) >= hsl_mcpreferredemailfrequency
    OR (LEN(ISNULL(hsl_mcpreferredemailfrequency,'')) != 0)
    OR (LEN(ISNULL(hsl_lastsentdate,'')) != 0)
)
Group by sent.[subscriberkey]

Answer 1

This is because all your columns in select statement needs to be in the group by clause. Ideally, it should only be the subscriber key / job id(In case you are trying to find the max sent date for a job). Refer to this article

Answer 2

I would recommend aggregating the _sent data first and join your Contact_BrandPref_CCV to the aggregated results. This can be done in a single query step by using a subquery.

However, this query could preform more slowly over time, as _send grows. If this becomes an issue, I would recommend coping the subquery into it's own query step. Then update this query to reference the results from the separate query step.

SELECT
createdon,
firstname,
lastname,
hsl_forecordid,
address1_telephone2,
address1_telephone1,
hsl_customertype,
birthdate,
hsl_dealseeker,
hsl_employee,
hsl_formertweemployee,
hsl_giftmember,
hsl_id18salesforcelegacy,
hsl_leadsource,
hsl_local,
hsl_longestmembership,
hsl_mandarin,
hsl_nameofpartnersignificantother,
hsl_napaneighbor,
hsl_nationaldonotcall,
hsl_phoneunsubscribe,
hsl_tweboardmember,
middlename,
msdyn_gdproptout,
new_globaloptout,
nickname,
modifiedon,
hsl_bouncedate,
hsl_bouncedemail,
hsl_bouncedemailreason,
hsl_lastclickdate,
hsl_lastopendate,
hsl_lastsentdate,
hsl_retiredfrommarketingcloud,
emailaddress1,
hsl_listofpurchasedvarieties,
hsl_priorprimaryemail,
hsl_totalbottleslastyear,
hsl_lastorderdate,
hsl_totalbottles,
hsl_highestbottleprice,
hsl_clubmembership,
hsl_countoforders,
hsl_activeclubmemberships,
hsl_paidforwinelastyr,
hsl_paidforwineytd,
hsl_clubtotaltenure,
hsl_numberoforderslastyear,
hsl_totalpaidforwine,
hsl_highestordervalue,
hsl_totalordervaluelastyear,
hsl_totalordervalue,
hsl_totalofbottleslastyear,
hsl_totalpaidforcorporategifting,
contactid,
displayrow,
hsl_donotallowemails,
hsl_donotallowmails,
hsl_donotallowphonecalls,
hsl_donotallowsms,
hsl_donotcallmobile,
hsl_legacyid,
hsl_brandpreferenceid,
hsl_mccampaignunsubscribedfrom,
hsl_name,
hsl_leadsourcename,
hsl_brandname,
hsl_mcpreferredemailfrequency,
statecode,
hsl_winerytrade,

sub.[SendDate],
sub.[subscriberkey]

FROM Ent.Contact_BrandPref_CCV 
INNER JOIN (
    select sent.SubscriberKey, MAX(sent.[eventdate]) AS [SendDate]
    from _sent AS sent
    group by sent.SubscriberKey 
) sub on [contactid] = sub.[subscriberkey]

WHERE DATEPART(month, DATEADD(day, 0, birthdate)) = DATEPART(month, GETDATE())
AND DATEPART(day, DATEADD(day, 0, birthdate)) = DATEPART(day, GETDATE())
AND hsl_donotallowemails = 'false'

AND (
    DATEDIFF(DAY, sub.eventdate, GETDATE()) >= hsl_mcpreferredemailfrequency
    OR (LEN(ISNULL(hsl_mcpreferredemailfrequency,'')) != 0)
    OR (LEN(ISNULL(hsl_lastsentdate,'')) != 0)
)