Compare group by sql server values

Asked

Viewed 156 times

1

Something very unusual happened in my bank. Some records were entered more than once (7 to 13) to be exact. This occurred due to software error that has already been solved.

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

  • The records that were not moved, I can leave only the smallest Id;
  • The records that all copies were moved together I leave only the smallest Id;
  • but by some trick of 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 follow.

Getting to the heart of the matter, is it possible to make an "if" within a select? I made this query that returns to 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 the values.

I wonder if there is way in this case select the Id of the second option, however, in the cases below it return 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 I mean, I must return the Id of the first and the second.

I hope I was clear.

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

sql sql-server query

  • I’m not sure I understand: you need to make a MIN(id) when in a case, and MAX(id) when in another? What is the criterion for knowing when to do the MAX(id)? At first I would suggest using CASE, but depending on the complexity of the criterion it may be necessary to use "window function", as OVER()...

  • Recurring problem, a Google search guarantee will bring almost ready solutions "remove duplicated records sql server"

  • How do you know which record is correct?

  • @Motta I think you don’t understand, the duplicate records are not the problem, the problem is that some duplicate records were not moved together, only one was and its copies were not, so this one was should be kept and its copies deleted

  • @Sorack It is a rule somewhat simple, let’s say that I have a record with 7 identical copies, what should remain is the oldest, ie the smallest Id, but if this record is different from your copies, the different should be maintained.

  • @nunks.lol Almost this, it would not be a MAX but the Id of the record that is different.

Show 1 more comment

1 answer

1


Group the results by counting them. After that take the minimum with the fewest 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 on SQL Fiddle.

  • The code is wrong. I suggest you test before posting.

  • @Josédiz I suggest you make the base available to me for testing then. I did not see an answer from you, which indicates that you were too lazy to try to solve. If you don’t have that ability, don’t be critical. It seems your attitudes are to disturb and not to help, which makes no sense in an aid forum. Look for another hobby.

  • @Josédiz ready, added Fiddle with the proof of the answer. I await the removal of the downvote. If you want you can use the bank I created, since so far apparently you have not had "time" to create one to answer.

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.