Mysql : Where in inner select vs where in outer select - different results

Question

I have a database with two tables as follow :

Where I want to get the latest Log for each user as :

I can get the result by the query (Q1) :

select u.login, tempLogs.createdDate from User as u,
    (select * from Logs as n1 order by n1.createdDate desc ) as tempLogs
where tempLogs.userId = u.id
group by u.login;

But this one (Q2) doesn't work:

select tempCK.login, tempCK.createdDate from
  (select u.login as login, n1.createdDate  as createdDate from Logs as n1 , User as u
       where n1.userId = u.id
       order by n1.createdDate desc 
  ) as tempCK
group by tempCK.login

I expected the result of inner select in Q2 to be ordered and the Q2 group by to take first of each result so the Q2 would be equivalent to Q1 !

Can someone help me to get the difference ?

score 0 · Answer 1 · answered Sep 10 '22 at 11:30

0

You can get the latest createdAt from table2 and then can join it with table1 -

SELECT DateLog, login UserLogin
  FROM table1 t1
  JOIN (SELECT userId, MAX(createdAt) DateLog
          FROM table2
         GROUP BY userId) t2 ON t1.id = t2.userId

answered Sep 10 '22 at 11:30

Ankit Bajpai

13,128
4
25
40

we can rewrite it to : SELECT Max(L.DateLog), U.login as UserLogin FROM Logs as L, User as U WHERE L.userId = U.id GROUP BY U.login – timmz Sep 10 '22 at 16:33

score 0 · Answer 2 · answered Sep 10 '22 at 11:51

I would suggest re-writing your query as per Ankit's answer as it guarantees to always provide the correct result, however in response to "why" your 2 methods were different:

I believe this happens because the ORDER BY clause in a subquery is ignored by modern optimizers.

score 0 · Answer 3 · edited Sep 10 '22 at 16:56

https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html says:

The optimizer propagates an ORDER BY clause in a derived table or view reference to the outer query block if these conditions are all true:

The outer query is not grouped or aggregated.

The outer query does not specify DISTINCT, HAVING, or ORDER BY.

The outer query has this derived table or view reference as the only source in the FROM clause.

Otherwise, the optimizer ignores the ORDER BY clause.

In your case, you are using GROUP BY in the outer query, so that spoils the first condition above. Therefore the ORDER BY i the derived table is ignored.

Your query is a variation of the greatest-n-per-group type of query. Questions about this type of query are asked almost daily on Stack Overflow (but not always tagged as such). There are several types of solutions, such as the answer given by @AnkitBajpai, and others. You can follow that tag to see other answers. For example, in my answer to Retrieving the last record in each group - MySQL

In the Q1 (which is getting correct result) also having an outer group by, maybe i'm missing something fundamental ? — timmz, Sep 10 '22 at 16:32

Mysql : Where in inner select vs where in outer select - different results

3 Answers3