SteveF SteveF - 3 months ago 16
MySQL Question

Using MAX in a JOIN statement

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

Answer

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.