Check and count if a column value is used in another column of same table

Question

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

• danblack: 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.

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;

fidddle