I have a table like this

enter image description here

and looking for a result like this, where the last modified date of a particular id is shown that too if the check is 1. Here id 6 is not in the result because the check is 0, also the 11/4/19 entry of 1 is ignored as the check is 0, so 9/4/19 entry is shown in result.

Next is the children count. it counts the number of times,the parent id is repeated. I really don't want the number of times,the parent id is repeated,but just want to know if its has child or not.

enter image description here

I am confused how to get the children count. Remaining i did like this

SELECT id, modified,checks 
FROM ( select * 
       from test 
       order by modified desc) as t1 
where checks!=0  group by (id) 

and i got this result

enter image description here

How to get children count too or just if the id got a child? How to get a result like this

enter image description here

  • ordering by without a limit in a subquery isn't a reliable ordering. Recursive CTEs in MySQL-8.0 is the only sane way to count subchildren.
    – danblack
    Apr 12, 2019 at 1:46

1 Answer 1

SELECT t1.id, 
       MAX(CASE WHEN t1.`check` THEN t1.modified_date END) modified_date,
       COUNT(DISTINCT t2.id) children_count,
       1 `check`
FROM test t1
LEFT JOIN test t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING modified_date IS NOT NULL;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.