How to detect level in nested MySQL table?
in my opinion structure not normalised, but if need resolve it as is

SELECT 
t1.id, t1.name,
CASE WHEN parent_id = 0 THEN
       1
ELSE
    (SELECT count(id)+1 from Table t2 WHERE t2.parent_id = t1.parent_id and t2.id &lt; t1.id) 
END AS seq_no 
    FROM Table t1


there are many modifications possible, depending from what finally You need, such as if need select seq_no for single name