Polygon numbering using virtual layer and polyline in QGIS

Question

I would like to number each polygon that is crossed by a polyline. For every polyline entity, the numbering has to be reset from 1.

I would like to keep the same logic of numbering incrementation used while digitizing the orignal polyline, defined by start_point (number = 1) and end_point.

at the moment I used this code made by JGH that number polygon, but the numbering doesn't reset at 1 at every polyline entity :

    select geometry, ROW_NUMBER() over (order by id, lpcent) as ID FROM 
(
select st_intersection(l.geometry, p.geometry) as geometry,
       l.id,
       ST_Line_Locate_Point(l.geometry, st_startpoint(st_intersection(l.geometry, p.geometry))) as lpcent
from p
 join l
 on st_intersects(l.geometry, p.geometry)
)

Answer 1

Change the first line to

select geometry, ROW_NUMBER() over (partition by id order by lpcent) as ID FROM 

"For each line id: number it in the order of lpcent"

See SQL Window Functions