I am trying to query the _Click data view to find the top performing links in any given job. I have wrote my query to report back the top 3 links in every job, to be grouped by job id, link, then links clicked descending by counting the subscriber key.
SELECT c.JobID, c.LinkContent, count(c.SubscriberKey)
FROM _Click c
FROM _Click as d
WHERE d.LinkContent = c.LinkContent AND count(d.SubscriberKey) >= count(c.SubscriberKey)
ORDER BY c.JobID, c.LinkContent, clicked DESC
This code generates syntax error requiring that I have TOP in my select statement which I don't want because I have no idea how many rows (JobID) it will return.
I also tried removing the ORDER BY line but it generates a new syntax code stating that I cannot use an aggregrate in the WHERE clause. For those curious, I adapted this code from: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/