Query for delete cloned records

Question

User110780343 posted

I need a query for do this: when I've records with same IDDipendente and IDAttivita and IDMansione I want to keep only 1 record (not important if first or second or last... I need to keep one and delete others)

so if I've record with

IDDipendente | IDMansione | IDAttivita| 
1 | 55 | 18
1 | 55 | 18
1 | 55 | 18
2 | 55 | 18
3 | 40 | 18
4 | 55 | 18

I need

1 | 55 | 18
2 | 55 | 18
3 | 40 | 18
4 | 55 | 18

Can you help me?

Here is table

CREATE TABLE [dbo].[DipendentiAttivita](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IDDipendente] [int] NULL,
[IDMansione] [int] NULL,
[IDAttivita] [int] NULL,
[Attivo] [bit] NULL,
 CONSTRAINT [PK_DipendentiAttivita] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks

Answer 1

User452040443 posted

Hi,

Try:

with CTE_RN as
(
    select
        *,
        row_number() over(partition by IDDipendente, IDAttivita, IDMansione order by ID) as RN
    from DipendentiAttivita
)

delete from CTE_RN where RN > 1

Hope this help

Answer 2

User1535942433 posted

Hi dandi.box,

As far as I think,without using CTE and ROW_NUMBER() you can just delete the records just by using group by with MAX function here is and example.

DELETE
FROM DipendentiAttivita
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM DipendentiAttivita
GROUP BY IDDipendente, IDMansione, IDAttivita)

Result:

Best regards,

Yijing Sun