0

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.

Improve this question
6
  • 1
    How are you determining which id to return? What is num? It is not in your data. Commented Oct 3, 2019 at 2:04
  • num is unique field Commented Oct 3, 2019 at 2:07
  • @programmer_25, this is baffling, i thought you want to delete? and your unique Id is num? Commented Oct 3, 2019 at 2:25
  • I edit it to id not num thanks for your helping to me Commented Oct 3, 2019 at 2:34
  • you accepted an answer before editing. Commented Oct 3, 2019 at 2:59

2 Answers 2

Reset to default
1

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
               );
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

@programmer_25, this is baffling, i thought you want to delete? and your unique Id is num?
Yes i want to delete but i will save this result in table and delete this one and i will back again insert again the right rows
and why are you doing that? while you can directly delete
How ? to do in this query : select id_emp, max(id) as id, date, min(ch_in), max(ch_out) from ch_inout group by id_emp, date; , i tried to write delete but it could not work
@programmer_25 . . . You can't. You are probably best off saving the results in a temporary table, truncating the original table and re-inserting the values.
|
0

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
Improve this answer

1 Comment

I tried as your query and still duplicated , it is strange !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.