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

Question

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

Madhur Bhaiya · Accepted Answer · 2018-11-13T21:16:32.840

1

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

edited Nov 13 '18 at 21:16

answered Nov 13 '18 at 21:02

Madhur Bhaiya

28,155
10
49
57

Thanks - I am getting a SQL error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select h.score from histories h where h.user_id' at line 4 – bhttoan Nov 13 '18 at 21:10
@bhttoan added a pair of parentheses. Please check the updated answer and let me know – Madhur Bhaiya Nov 13 '18 at 21:17
Yes that seems to have sorted it now! – bhttoan Nov 13 '18 at 21:20

Shidersz · Answer 2 · 2018-11-13T21:47:41.610

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

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

2 Answers2