select progressivo from (            
select progressivo, idnatante from (
select  @riga:= @riga + 1 AS progressivo,T.*
FROM (SELECT TipoNatante,idnatante
FROM natante 
order by TipoNatante) as T, (SELECT @riga := 0) as V) as Z) as W where W.idnatante = 66;

o

select progressivo, idnatante from (
select  @riga:= @riga + 1 AS progressivo,T.*
FROM (SELECT TipoNatante,idnatante
FROM natante 
order by TipoNatante) as T, (SELECT @riga := 0) as V) as Z where Z.idnatante = 66;



Mysql Server Version 8.0.18   X64     da come risultato progressivo = 10

Mysql Server Version 8.0.26   X64     da come risultato progressivo = 1