I have a data extension that is created using SQL query on hidden tables that record tracking info related to sent emails. Each table contains JOB ID which is the key to associating the tracking data. Tables include _Job, _Open, _Send, _Click, _Bounce etc.
I have an overwrite query with SUBSCRIBERID and JOBID as primary keys so that there is a single record for each email sent to an individual subscriber. Then three subsequent queries that update each additional field in my data extension sequentially.
The _Open, _Click, and _Bounce tables include multiple records for each job (not unique open for example). So I need to use GROUP BY to get a single record. The issue is that I need the minimum record (first time email is opened) and not just random. It appears that the min() function is not doing anything...
select min(opens.[EVENTDATE]) as [OpenDate] , opens.[JOBID] as [JobID] , opens.[SUBSCRIBERKEY] as [ContactID] from _Open opens WITH (NOLOCK) group by opens.[JOBID], opens.[SUBSCRIBERKEY]
This query works, but does not return the earliest date of email open from the _Opens table. So I tried adding "order by":
select min(opens.[EVENTDATE]) as [OpenDate] , opens.[JOBID] as [JobID] , opens.[SUBSCRIBERKEY] as [ContactID] from _Open opens WITH (NOLOCK) group by opens.[JOBID], opens.[SUBSCRIBERKEY] order by opens.[EVENTDATE]
but that is throwing an error:-
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Do you know how the TOP statement works, and how to apply it to this query? Or any other suggested solution?