Compare values in group by sql server

Question

Compare values in group by sql server

Navigation

#1 by (1 votes)

1

Something very unusual happened in my bank. Some records have been entered more than once (7-13) to be more accurate. This was due to a software error that has already been resolved.

Now I need to delete duplicate records, but I have the following scenario:

Records that have not been moved, I can leave only the smallest Id;
Records that all copies were moved together I leave only the smallest Id;
But for some cute fate, I have the third case. Some duplicates moved alone, that is, of the 7 copies only one was correct and the others did not.

Getting to the heart of the question, is it possible to do an "if" within a select? I have done this query which returns me the following:

SELECT    IdLoteProduto ,
          Serie ,
          Saiu ,
          COUNT(Id) AS cont ,
          MIN(Id) AS old
FROM      dbo.SerieProduto
WHERE     empresa = 385
      AND IdLoteProduto = 264
GROUP BY  IdLoteProduto ,
          Serie ,
          Saiu

This query returns me the following information:

Id  Serie               Saiu Rep  Id mais antigo
264 8955031700194659020 0    6    2217691
264 8955031700194659020 1    1    2217721

This in a query that returns all values to me.

I would like to know if there is a way to select the Id of the second option in this case, but in the cases below it gives me the correct numbers.

Id  Serie               Saiu Rep  Id mais antigo
264 8955031700194659020 0    7    1234567
264 8955031700194659021 1    7    1234897

In this case they are different serials ie, you should give me the first and second ID's.

I hope I have been clear.

Please, if you can improve my question, tell me how, what I will do.

sql sql-server query

asked by anonymous 09.10.2017 / 22:00

1 answer

Standardize error messages in DataAnnotations in ViewModels classes Oracle - Error connecting to ADOConnection Delphi 10.1 (Berlin)

score 1 · Accepted Answer

Group results by counting them. After that get the minimum with the fewest number of repetitions.

WITH repeticoes AS (
  SELECT sp.empresa,
         sp.idloteproduto,
         sp.serie,
         sp.saiu,
         sp.id,
         COUNT(sp.id) AS quantidade
  FROM dbo.SerieProduto sp
  WHERE sp.empresa = 385
    AND sp.idloteproduto = 264
  GROUP BY sp.empresa,
           sp.idloteproduto,
           sp.serie,
           sp.saiu,
           sp.id
)
SELECT r.empresa,
       r.idloteproduto,
       r.serie,
       r.saiu,
       MIN(r.id) AS old
  FROM repeticoes r
 WHERE NOT EXISTS(SELECT 1
                    FROM repeticoes r2
                   WHERE r2.empresa = r.empresa
                     AND r2.idloteproduto = r.idloteproduto
                     AND r2.serie = r.serie
                     AND r2.saiu = r.saiu
                     AND r2.quantidade < r.quantidade)
 GROUP BY r.empresa,
          r.idloteproduto,
          r.serie,
          r.saiu,
          r.quantidade

See working in SQL Fiddle .