CODEWITHSUNDEEP
sqlpostgresql
notentered
notentered

Reputation: 145

sql query for deleting similar rows

I am having a trouble with following cases:

  1. duplicate except id(primary key)
  2. all fields are the same except id(primary key) and finish_time

enter image description here

My question is how to remove one row from the 1st result, but also remove the row that has null value in finish_time field. The final expected result is the one below: enter image description here

Thank you so much in advance!

Upvotes: 0

Views: 47

Answers (2)

lat long
lat long

Reputation: 930

like this

    delete from t
    where id IN
    (select distinct on (task_id, date) t.id
    from t
    order by task_id, date, finish_time nulls last) as sub

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can keep one row using distinct on:

select distinct on (task_id, date) t.*
from t
order by task_id, date, finish_time nulls last;

Upvotes: 2

Related Questions