How to remove duplicate values from datatable SQL

Question

Getting values duplicate:

╔══════╦══════╦═══════╦════════════╦═════════╦═════════╦══════╦═══════╗
║ ID   ║ Name ║ Class ║ Date       ║ Intime  ║ Outtime ║ INAM ║ OUTPM ║
╠══════╬══════╬═══════╬════════════╬═════════╬═════════╬══════╬═══════╣
║ 1001 ║ Paul ║ 1st   ║ 29-11-2022 ║ Holiday ║ Holiday ║ H    ║ H     ║
╠══════╬══════╬═══════╬════════════╬═════════╬═════════╬══════╬═══════╣
║ 1001 ║ Paul ║ 1st   ║ 29-11-2022 ║ Holiday ║ Holiday ║ H    ║ H     ║
╠══════╬══════╬═══════╬════════════╬═════════╬═════════╬══════╬═══════╣
║ 1001 ║ Paul ║ 1st   ║ 29-11-2022 ║ Holiday ║ Holiday ║ H    ║ H     ║
╚══════╩══════╩═══════╩════════════╩═════════╩═════════╩══════╩═══════╝

Code:

SELECT DISTINCT COALESCE(tt.ID,t1.ID) AS ID,
          COALESCE(tt.Name,t1.Name) AS Name,
          COALESCE(tt.Class,t1.Class) AS Class,tt.Date,    
          COALESCE(tt.Intime,t1.Intime) AS Intime,
          COALESCE(tt.Outtime,t1.Outtime) AS Outtime,
          COALESCE(tt.INAM,t1.INAM) AS INAM,
          COALESCE(tt.OUTPM,t1.OUTPM) AS OUTPM
FROM stuattrecordAMPM AS t1
CROSS JOIN (SELECT * FROM stuattrecordAMPM  UNION ALL
            SELECT null,null,null,Date,Holiday_Name,Holiday_Name,Status,Status FROM HolidayList) AS tt 
            order by [ID]
            DELETE FROM stuattrecordAMPM
WHERE Date IS NULL

In this code I'm getting duplicate values. How to avoid duplicates from datatable?

Answer 1

You can give a row number to each row grouped by all the columns, then delete the rows having row number greater than 1.

Query

with cte as(
  select *, row_number() over(
    partition by [id], [name], [class], [date], [intime], [outtime], [inam], [outpm]
    order by [id]
  ) as [rn]
  from [your_table_name]
)
delete * from cte
where [rn] > 1;