0

I have a table with comments related to articles.

entity_id | parent_id | comment | created_at
     1    |     4     |  AAAA   | 2018-10-18 12:28:03
     2    |     5     |  BBBB   | 2018-10-19 12:28:03
     3    |     4     |  CCCC   | 2018-10-19 12:28:03
     4    |     5     |  DDDD   | 2018-10-20 12:28:03
     5    |     5     |  EEEE   | 2018-10-22 12:28:03
     6    |     4     |  FFFF   | 2018-10-25 12:28:03
     7    |     4     |  FFFF   | 2018-10-29 12:28:03

Now I'd like to get the latest comment for each article, so in the example above I'd need the entity_id 5 and 7.

My idea is to ORDER BY into the sub-query and then GROUP BY to have the data filtered.

The following query is not really working

SELECT *
FROM (SELECT *
FROM `test_table`
ORDER BY entity_id DESC
) AS table1

I'm still getting the same results as shown in the example above.

Would you tackle the problem in a different way? What's wrong with the sub-query?

I'm using MySQL version: 5.5.5-10.1.38-MariaDB

  • A derived table (your subquery) is, by definition, unordered. That is, the ORDER BY is ignored. What you have is a "groupwise-max" problem. See the tag that you already have for multiple approaches. – Rick James Jun 20 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.