Home / Questions / 31156
Accepted
German AT
Asked: 2020-11-02 12:05:46 +0800 CST 2020-11-02 12:05:46 +0800 CST

How to filter repeated strings in SQL

  • 772

I am using SQL 2008 to query using inner join:

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' GROUP BY t2.intencion_id,numpago,importe,t1.estatus,intfinanciamiento_interes,t3.orden order by intencion_id,numpago,orden desc

among the columns that this query returns, there are 3 that are repetitive series: intention_id, numpago and order query result but I want it to show the records of all the intention_id, all the numpago but only the highest order

How can I do this filtering?

sql

2 Answers

  1. Best Answer

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

    If that is the case, this can be achieved 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's probably a better way to achieve what you're asking for, but you'd have to describe your tables better.

    • 2

  2. I don't fully understand the data but I'm going to assume the following:

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

    If the above is 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 byso if you confirm that this solution works it might help you to write it in other more optimal ways.

    • 0