Tell me why the two queries produce different results?

1st request:

SELECT date, text, topic_id FROM forum_msgs ORDER BY topic_id,date DESC

and 2nd request:

 SELECT * FROM (SELECT date, text, topic_id FROM forum_msgs ORDER BY topic_id,date DESC ) t1

The second query produces an unsorted table, and therefore the query that is shown below no longer makes sense.

 SELECT * FROM (SELECT date, text, topic_id FROM forum_msgs ORDER BY topic_id,date DESC ) t1 GROUP BY topic_id
  • because only the result table of the subquery is sorted. External select selects from this "table" and honestly do not know by what principle sorts! - JVic
  • A GROUP BY query always precedes an ORDER BY clause. If not difficult to describe, what problem are you solving? What do you want to achieve? Perhaps there are other ways. - cheops
  • GROUP BY does not know how to work with pre-sorted data, it sorts them by itself as it sees fit. You are probably writing to select * and making group by expect that it returns for each group some specific value of some specific field. BUT what value it will give is undefined, do not think that it will give for example the first value. he has the right to give arbitrary. In addition, in many versions of MySQL, it is already forbidden to use columns with no aggregate functions in group by by default in queries. - Mike
  • The task is simple .. for each topic_id find the last entry (by date) - vuler
  • one
    Look at these questions here: ru.stackoverflow.com/questions/496515 and ru.stackoverflow.com/questions/545054 and plus to this select * from table where (topic_id, date) IN(select topic_id, max(date) from table group by topic_id) - Mike

2 answers 2

Thanks Mike - suggested what was needed. The final query is of course a bit more complicated, but the basis was laid with a bang.

 SELECT t1.parent_id, t1.id id, t1.name, t2.* FROM forum_dirs t1 LEFT JOIN ( SELECT forum_topics.title, forum_topics.dir_id, forum_topics.id topic_id, forum_topics.date topic_date, forum_topics.user_id topic_user_id, users.logo topic_user_logo, users.name topic_user_name, users.login topic_user_login, t3.* FROM forum_topics LEFT JOIN users ON users.id=forum_topics.user_id LEFT JOIN ( SELECT forum_msgs.text, forum_msgs.user_id msg_user_id, forum_msgs.date msg_date, forum_msgs.topic_id msg_topic_id, users.logo msg_user_logo, users.name msg_user_name, users.login msg_user_login FROM forum_msgs LEFT JOIN users ON users.id=forum_msgs.user_id WHERE (topic_id, date) IN (SELECT topic_id, max(date) FROM forum_msgs GROUP BY topic_id) ) t3 ON t3.msg_topic_id=forum_topics.id WHERE (dir_id, date) IN (SELECT dir_id, max(date) FROM forum_topics GROUP BY dir_id) ) t2 ON t2.dir_id=t1.id

    Probably because in the second case you are sampling from the sample. sampling goes again in the order convenient to the optimizer. And why not use group by in the same expression as order by? They may well coexist in the same sample.

    • In one request, he will first do Group by, as it is convenient for him, and only then will sort what happened, but in the reverse order. Although the task is elementary - choose the last entry in each topic_id by the latest date, and not because they are in the table. :( - vuler