CODEWITHSUNDEEP
mysqlsql
Anon
Anon

Reputation: 1221

GROUP BY with the most recent elements

Generally we use SELECT MAX(datetime) with GROUP BY(page_ID) to get the most recent pages but I don't understand why it doesn't work here.

My following query should only select rows ID 75 and 77 but its selects 75 and 76 : enter image description here

SELECT ru.page_ID AS id, 
    pFrom.name AS name,
    UNIX_TIMESTAMP(ru.rating_time) AS action_date,                          
    ru.current_rank AS current_rank,        
    MAX(ru.rating_time)                     
FROM ranks_update ru
INNER JOIN pages pFrom
    ON ru.page_ID = pFrom.page_ID
WHERE ru.ranking_ID = :id_rk
    AND ru.page_ID IN ( ** subquery 1 **)
    AND ru.rating_time >= ( ** subquery  2 **)                      
GROUP BY ru.page_ID                             
ORDER BY ru.current_sum_vote DESC

Upvotes: 0

Views: 92

Answers (2)

podiluska
podiluska

Reputation: 51514

A contributory factor will be MySQL's quirky interpretation of GROUP BY. In standard SQL you have to specify all non aggregated columns (eg: GROUP BY ru.page_ID, pFrom.name, ru.currentRank, ru.current_sum_vote). MySQL will allow you not to do that, but its behaviour is undefined and quite possibly random. If you write standard SQL, you'll get predictable results.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

select ru.page_ID AS id, 
    pFrom.name AS name,
    UNIX_TIMESTAMP(ru.rating_time) AS action_date,                          
    ru.current_rank AS current_rank,        
    rum.MaxDate
from ranks_update ru
inner join(
    select page_ID, MAX(rating_time) as MaxDate
    from ranks_update
    WHERE ranking_ID = :id_rk
        AND page_ID IN ( ** subquery 1 **)
        AND rating_time >= ( ** subquery  2 **)   
    group by page_ID
) rum on ru.page_ID = rum.page_ID and ru.rating_time = rum.MaxDate
inner join pages pFrom on ru.page_ID = pFrom.page_ID
ORDER BY ru.current_sum_vote DESC

Upvotes: 2

Related Questions