How to use SQL ORDER BY statement in marketing cloud (error says requires TOP)

Question

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?

Answer 1

I have tested the query on my end and it seems to be getting the MIN date as shown below,

1. NOT GROUPED data view query/screenshot showing 2 JobID/ContactID records with the same values but 2 different datetime stamps using the following query:

SELECT opens.[EVENTDATE] AS [OpenDate]
, opens.[JOBID] AS [JobID]
, opens.[SUBSCRIBERKEY] AS [ContactID]
FROM _Open opens WITH (NOLOCK)

---

2. GROUPED data view query/screenshot showing a single record JobID/ContactID records with the MIN datetime stamp using the following query:

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]

Hope this clears things up for you.

NOTE: if you ever wish to do an ORDER BY in future, use this following line of code

ROW_NUMBER() OVER (PARTITION BY SubscriberKey, JobID ORDER BY EventDate ASC) AS Row

then in your WHERE clause do this

WHERE Row = 1

this will return the first record only which will be your minimum datetime.

Answer 2

I would suggest using a subquery to rank the event dates by subscriber and trying this:

select
  x.eventdate
, x.jobid
, x.contactid
from (
    select 
      o.eventdate
    , o.jobid
    , o.subscriberkey contactid
    , row_number() over (partition by o.subscriberkey order by o.eventdate asc) as row
    from _open o
) x
where x.row = 1

References :

• Row_Number()