Does MAX(eventdate) work in SFMC

Question

I wanted to know if MAX(Eventdate) works in SFMC as its not working for me. I'm trying to only extract customer who have opened the email and only want to pull the records with most recent time/date as the extract contains dupes.

The query I'm using is below. I only want to pull all customers who has opened their email yesterday and with the email name below

SELECT 
    Max(EventDate) as Eventdate,
    IsUnique,
    SubscriberKey,
    SubscriberID 
FROM _open o
JOIN _Job j
    ON j.jobid = o.jobid 
WHERE eventdate >= DATEADD(dd,-1,getdate())
    AND EmailName = 'EW01 welcome email'

Answer 1

Answer

Yes it does work. For a list of some functions available in SFMC visit : SQL Queries.

As you havent provided your error, I suspect its not working because you havent used the GROUP BY clause. Whenever you use aggregate values, you have to group by everything else that isnt aggregated.

Try this:

SELECT 
    Max(EventDate) as Eventdate,
    IsUnique,
    SubscriberKey,
    SubscriberID 
FROM _open o
JOIN _Job j
    ON j.jobid = o.jobid
WHERE eventdate >= DATEADD(dd,-1,getdate())
    AND EmailName = 'EW01 welcome email'
GROUP BY IsUnique, SubscriberKey,SubscriberID `

UPDATE

From your comments , I advise you not to use MAX and use the query below instead.

SELECT 
    Eventdate,
    IsUnique,
    SubscriberKey,
    SubscriberID
FROM 
    (SELECT 
        Eventdate,
        row_number()over(partition by SubscriberKey order by eventdate asc) as row
        IsUnique,
        SubscriberKey,
        SubscriberID 
    FROM _open o
    JOIN _Job j
        ON j.jobid = o.jobid 
    WHERE eventdate >= DATEADD(dd,-1,getdate())
        AND EmailName = 'EW01 welcome email'
    GROUP BY
        IsUnique,
        SubscriberKey,
        SubscriberID
    ) x
WHERE x.row = 1

A bit more lines of code but gets the job done and is by far my prepared method of deduping by order