CODEWITHSUNDEEP
sqlsql-server
user4994068
user4994068

Reputation:

Delete duplicated record

I have a table which contains a lot of duplicated rows like this:

 id_emp  id       date      ch_in        ch_out
    1   34103   2019-09-01      
    1   34193   2019-09-01  17:00
    1   34194   2019-09-02  07:03:21    16:59:26
    1   34104   2019-09-02  07:03:21    16:59:26
    1   33361   2019-09-02  NULL        NULL

I want just one row for each date and others must delete with condition like I want the output must be: 

       id_emp  id       date      ch_in        ch_out
        1   34193   2019-09-01  17:00
        1   34104   2019-09-02  07:03:21    16:59:26

I tried to use distinct but nothing working:

select  distinct id_emp, id, date_1, ch_in,ch_out
from ch_inout
where id_emp=1 order by date_1 asc

And I tried too using this query to delete:

select *
from (
  select *, rn=row_number() over (partition by date_1 order by id)
  from ch_inout 
) x
where rn > 1;

But nothing is working the result is empty.

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You can use aggregation:

select id_emp, max(id) as id, date, min(ch_in), max(ch_out)
from ch_inout
group by id_emp, date;

This returns the maximum id for each group of rows. That is not exactly what is returned in the question, but you don't specify the logic.

EDIT:

If you want to delete all but the largest id for each id_emp/date combination, you can use:

delete c from ch_inout c
    where id < (select max(c2.id)
                from ch_inout c2
                where c2.id_emp = c.id_emp and c2.date = c.date
               );

Upvotes: 1

GMB
GMB

Reputation: 222722

You can use ROW_NUMBER() to identify the records you want to delete. Assuming that you want to keep the record with the lowest id on each date:

SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER(PARTITION BY date ORDER BY id) rn
    FROM ch_inout t
) x
WHERE rn > 1

You can easily turn this into a DELETE statement:

WITH cte AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER(PARTITION BY date ORDER BY id) rn
    FROM ch_inout t
)
DELETE FROM cte WHERE rn > 1

Upvotes: 0

Related Questions