CODEWITHSUNDEEP
mysql
Lux Interior
Lux Interior

Reputation: 321

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

Upvotes: 0

Views: 57

Answers (2)

trincot
trincot

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

Matt Cremeens
Matt Cremeens

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

Related Questions