Mysql : Where in inner select vs where in outer select

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 ?

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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 @AnkitPajpai, 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