Home / Questions / 62452
Accepted
lois6b
Asked: 2020-04-13 03:12:22 +0800 CST 2020-04-13 03:12:22 +0800 CST

Remove duplicates... under certain guidelines

  • 772

I have a table Peticioneswhose layout is:

NPeticion       - nvarchar
FechaApertura   - date
TipoPeticion    - nvarchar
FechaResolucion - nvarchar
...
(más campos, irrelevantes para este asunto)

The issue is that I am trying to make a query that eliminates duplicates of records that have the same NPeticion, FechaAperturaand TipoPeticion keep the record with the FechaResolucionmost recent and different from 'NULL'unless all the records are 'NULL'that I would keep any of them.

I made this query: (possibly not the most elegant/efficient but it's what I came up with)

;with aBorrar as
(select [NPeticion], [FechaApertura], [TipoPeticion],[FechaResolucion],
    row_number() over(partition by [NPeticion], [FechaApertura] 
                      order by  [FechaResolucion] desc) rn
  from [dbo].[Peticiones]
  where NPeticion in (

          SELECT
            [NPeticion]
        FROM
            [dbo].[Peticiones]
        GROUP BY
            [NPeticion],FechaApertura, TipoPeticion
        HAVING 
            COUNT(*) > 1

            )
  and [FechaResolucion] <> 'NULL'
  Union 
  select [NPeticion], [FechaApertura], [TipoPeticion],[FechaResolucion],2
  from [dbo].[Peticiones]
  where NPeticion in (

          SELECT
            [NPeticion]
        FROM
            [dbo].[Peticiones]
        GROUP BY
            [NPeticion],FechaApertura, TipoPeticion
        HAVING 
            COUNT(*) > 1                
            )
  and [FechaResolucion] = 'NULL'
)
select * 
from aBorrar 
where rn > 1;

Explanation:

Since FechaResolucionit is a nvarchar, if I order descto keep the most recent, the values ​​appear first 'NULL'. That's why I do a select ordering the records that are not 'NULL'and then union of those null.

But that doesn't solve the problem if all the duplicates contain that column equal to 'NULL'I would delete them all...

How do I make it leave me a record in the event that all the duplicates have FechaResolucion = 'NULL'?

Data example:

[NPeticion]       [FechaApertura] [TipoPeticion] [FechaResolucion]
'20171204000001'     12/04/2017        'A'          '12/04/2017'
'20171204000001'     12/04/2017        'A'          '10/04/2017'
'20171204000001'     12/04/2017        'A'          '10/04/2017'
'20171204000001'     12/04/2017        'A'             'NULL'
'20171204000002'     12/04/2017        'B'             'NULL'
'20171204000002'     12/04/2017        'B'             'NULL'

Current result (records to be deleted):

[NPeticion]       [FechaApertura] [TipoPeticion] [FechaResolucion]
'20171204000001'     12/04/2017        'A'          '10/04/2017'
'20171204000001'     12/04/2017        'A'          '10/04/2017'
'20171204000001'     12/04/2017        'A'             'NULL'
'20171204000002'     12/04/2017        'B'             'NULL'
'20171204000002'     12/04/2017        'B'             'NULL'

Expected result (records to be deleted):

[NPeticion]       [FechaApertura] [TipoPeticion] [FechaResolucion]
'20171204000001'     12/04/2017        'A'          '10/04/2017'
'20171204000001'     12/04/2017        'A'          '10/04/2017'
'20171204000001'     12/04/2017        'A'             'NULL'
'20171204000002'     12/04/2017        'B'             'NULL'

Note: I have not designed the BD. Modifying its design is not possible.

sql-server

2 Answers

  1. Best Answer

    Some comments on your current code. One of your problems is that by simply doing ORDER BY FechaResolucion DESC, and being FechaResoluciona string with the date format dd/mm/aaaa, it will not sort correctly, but alphabetically.

    On the other hand, you don't need to separate two queries to see if they contain or not NULL, but you can do something like the following:

    WITH CTE1 AS
(
    SELECT  NPeticion,
            FechaApertura,
            TipoPeticion,
            NULLIF(FechaResolucion,'NULL') FechaResolucion
    FROM dbo.Peticiones
), CTE2 AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY NPeticion, FechaApertura, TipoPeticion
                                    ORDER BY CONVERT(DATETIME,FechaResolucion,103) DESC)
    FROM CTE1
)
SELECT *
FROM CTE2
WHERE RN > 1;

    In this case, the first CTE doesn't even need to be done separately, and you could use the second one by replacing it CONVERT(DATETIME,FechaResolucion,103)with CONVERT(DATETIME,NULLIF(FechaResolucion,'NULL'),103), but I prefer to leave it that way because I find it more readable.

    • 6

  2. I would simply order by FechaResolucion DESCwith a ROW_NUMBER()in a subquery, then filter by only Number = 1and it will bring you the most recent date or any of the Nulls in case they are all null

    ;WITH _peticiones AS(
SELECT ROW_NUMBER() OVER(PARTITION BY NPeticion ORDER BY CAST(P.FechaResolucion AS DATE) DESC) [Number],
    P.NPeticion, P.FechaApertura, P.TipoPeticion, CAST(P.FechaResolucion AS DATE) [FechaResolucion]
  FROM @Peticiones P 
) SELECT * FROM _peticiones P WHERE P.Number = 1
    • 1