Reputation: 321
I'm trying to return the max human_hits of distinct domains but have hit a wall. The statement I have pulls back distinct domains with human_hits, but it just grabs the first one, so it's not the max.
I tried adding MAX to the statement and experimenting but only managed to hang the database.
Here's what I have so far...
SELECT q.* FROM (
SELECT ah.datestamp, ad.domain, ah.human_hits
FROM `a_hits_hourly` ah
INNER JOIN a_saved_domains ad ON ah.domain_id = ad.domain_id
WHERE ah.datestamp > 2016070000 AND ah.human_hits > 0
) q
GROUP BY q.domain
Can anybody help?
Thanks
Steve
Upvotes: 0
Views: 57
Reputation: 351403
In case you need to know the other values, like datestamp
, that correspond to the records of your interest, you can use a query like this:
SELECT domain, datestamp, human_hits
FROM (
SELECT ah.datestamp,
ah.human_hits,
@rn := if (ad.domain = @domain, @rn + 1, 1) rn,
@domain := ad.domain domain
FROM a_hits_hourly ah
INNER JOIN a_saved_domains ad ON ah.domain_id = ad.domain_id
WHERE ah.datestamp > 2016070000
ORDER BY ad.domain,
ah.human_hits DESC
) q
WHERE rn = 1
The inner query sorts the hits in descending order per domain, adding a row count to it that resets at the start of the next domain. The outer query takes only those records that were numbered with a 1.
Upvotes: 0
Reputation: 5151
What about this?
SELECT ad.domain, MAX(ah.human_hits)
FROM `a_hits_hourly` ah
INNER JOIN a_saved_domains ad
ON ah.domain_id = ad.domain_id
WHERE ah.datestamp > 2016070000 AND ah.human_hits > 0
GROUP BY ad.domain
It should get you the maximum human_hits
for each domain
. Or perhaps I'm not sure what you are wanting.
Upvotes: 1