How to detect level in nested MySQL table? I have simple child-parent relation by saving parent_id. Is it possible to count which level each entry?

My DB looks like this:



As You can see from the image, for example the first item relation is:


Aston Martin


Vanquish


10

V8...
DB9
Rapide



When I selecting Aston Martin I need to know that it is first record, when Vanquish is the second, and so on.
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