How to filter repeated series in SQL

As I mentioned, the question is not very clear. But my interpretation is that for any series of records that share the same values for the combination intencion_id, numpago , you only want to return the record that has the highest value for the column orden .

If this is the case, this can be achieved by using row_number() to filter those records:

;with cte as (
    SELECT row_number() over (
              partition by t2.intencion_id, t2.numpago
                  order by t3.orden desc) as rnk,
           t2.intencion_id,
           t2.numpago,
           t3.orden,
           t2.importe,
           SUM([importeaplicado]) AS importeaplicado,
           t3.intfinanciamiento_interes,
           t1.[estatus] 
      FROM [APLICACIONES] as t1
      join movcargos as t2
        on t1.idmovcargo = t2.idmovcargo 
      join intenciones_financiamiento as t3
        on t2.intencion_id = t3.intencion_id
       and t2.numpago = t3.intFinanciamiento_pagoNumero
     where t2.idtipomovimiento = 1
       and t1.estatus = 'A'
     GROUP BY t2.intencion_id,
              numpago,
              importe,
              t1.estatus,
              intfinanciamiento_interes,
              t3.orden
)
select intencion_id,
       numpago,
       orden,
       importe,
       importeaplicado,
       intfinanciamiento_interes,
       [estatus]
  from cte
 where rnk = 1
 order by intencion_id,
          numpago

There is probably a better way to achieve what you ask for, but you should better describe your tables.

I do not fully understand the data but I will assume the following:

• All the row groups have the same fields orden , that is, if the orden greater is 5 , you will always get results with order 1, 2, 3, 4 y 5 of which you will always want the orden 5 .

If true, the solution would be this:

SELECT t2.intencion_id,t2.numpago,t3.orden,t2.importe
  ,SUM([importeaplicado]) AS importeaplicado
  ,t3.intfinanciamiento_interes
  ,t1.[estatus] FROM [APLICACIONES] as t1
inner join movcargos as t2 on t1.idmovcargo=t2.idmovcargo 
inner join intenciones_financiamiento as t3 on (t2.intencion_id=t3.intencion_id and t2.numpago=t3.intFinanciamiento_pagoNumero) 
WHERE t2.idtipomovimiento=1 
and t1.estatus='A' 
and t3.orden = max(select orden from intenciones_financiamiento)
GROUP BY t2.intencion_id, numpago, importe, t1.estatus, intfinanciamiento_interes, t3.orden 
ORDER BY intencion_id, numpago, orden desc

Of course I recommend using having by so if you confirm that this solution works it could help you write it in other more optimal ways.