Compare scores in two tables and show those where the most recent is different

user_1278201 0
Viewed 45

I have two tables in MariaDB where I need to show those in the left table where their current score is not the same as the most recent score in the history table.

For example:

users

id name    current_score
1  Bob     4
2  Tom     5
3  Fred    3
4  Tim     3
5  Ian     4


histories
id user_id score date 
1  1       3     2018-11-13
2  1       4     2018-11-12
3  1       2     2018-11-11
4  2       5     2018-11-12

In the above I would want to show Bob as his latest history is not the same as his current score but not show Tom as his is a match

I tried using something like:

SELECT u.id, u.name, u.current_score 
FROM users u 
where u.current_score not in 
(select h.score from histories h where 
h.user_id=u.id order by h.date desc limit 1)

This threw an error:

#1235 - This version of MariaDB doesn't yet support 
'LIMIT & IN/ALL/ANY/SOME subquery'

If I remove the limit 1 then it returns almost all the rows in users - there are a few thousands rows in each tables but I think it should return around 50 but it is returning over 4,100 rows out of 4,285 possible rows

mariadb groupwise-maximum mysql
2 Answers
  • Determine the latest history score in a Correlated subquery, within the Select clause itself.
  • Group By on the user, and use HAVING clause to consider cases where current score does not match the latest score in the history
  • I have to use MAX() aggregation function on the score values, so that it is a valid ANSI SQL compliant GROUP BY. It does not affect anything, as respective score values are one only (thus maximum only).

Try the following instead:

SELECT u.id, 
       u.name, 
       MAX(u.current_score) AS m_current_score, 
       MAX((select h.score 
           from histories h 
           where h.user_id = u.id 
           order by h.date desc limit 1)) AS history_score 
FROM users u 
GROUP BY u.id, u.name 
HAVING m_current_score <> history_score
user_24693080

One approach for what you need is using a sub-query for get the date related to the latest history entry for every user_id. After this, you can join again the table histories for get the rest of the columns associated to this latest date. This is summarized on next query:

SELECT
    u.id,
    u.name,
    u.current_score,
    h.score AS latest_score_from_history
FROM
    user AS u
LEFT JOIN
    -- This subquery gets the date of the latest history register for every user
    (SELECT
         user_id,
         MAX(date) AS maxDate
     FROM
         histories
     GROUP BY
         user_id) AS latest ON latest.user_id = u.id
LEFT JOIN
    histories AS h ON h.user_id = latest.user_id AND h.date = latest.maxDate
WHERE
    u.current_score <> h.score
user_103664950
Related