-1

I have a particular value that appears more than one time in a column of a table. I want to keep only one time for that record. How to do it? For example, from this table I want to keep 63 record for only one time. Please see the table.

enter image description here

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
nrad
  • 63
  • 1
  • 2
  • 9
  • Does this answer your question? [Delete duplicate rows and keep one row](https://stackoverflow.com/questions/9845046/delete-duplicate-rows-and-keep-one-row) – MustafaOmar Jul 09 '20 at 08:43

1 Answers1

1

You can use Row_Number() to give each duplicate a number, then delete which ever holds a value more than one

  delete  tbl
    from    ( SELECT 
                *,ROW_NUMBER() OVER (
              PARTITION BY 
                row_number
              ORDER BY 
                Date) rn
    where   rn > 1
MustafaOmar
  • 430
  • 4
  • 10
  • Hi Mustafa, I have 10 other dimensions and metrics, do i need to include all of it after PARTITION BY clause. – nrad Jul 09 '20 at 09:04
  • can explain with more details please ? – MustafaOmar Jul 09 '20 at 09:06
  • Please see the image. I have Date,Hour,Activity_Group_ID, Activity_ID, cost etc in the table. – nrad Jul 09 '20 at 09:12
  • you need to include in (PARTITION BY) clause the column that you do not want to have duplicate values. which is up to you – MustafaOmar Jul 09 '20 at 09:17
  • Hi Mustafa, Thanks for help. I'm using bigquery. It shows unexpected keyword FROM. – nrad Jul 09 '20 at 09:39
  • before everything please make sure that you have a copy of your data, as DELETE can not be reverted, and i think this link will be very helpful: https://stackoverflow.com/questions/48130324/bigquery-delete-statement-to-remove-duplicates – MustafaOmar Jul 09 '20 at 09:47
  • Hi Mustafa, As I've already calculated the duplicate column. Please see the table.I want to keep 63 record for only one time. – nrad Jul 09 '20 at 09:57
  • "where rn > 1" this part deletes any record with count more than one, which means the record holding rn =1 will remain – MustafaOmar Jul 09 '20 at 09:59
  • Thank you. I think this query will help me. delete from t1 from (SELECT *,row_number() OVER ( PARTITION BY row_number ORDER BY Date) rn from t1 where rn > 1. but still it shows the error. – nrad Jul 09 '20 at 10:03
  • @nrad . . . This does not look like correct MySQL syntax. How can this be the accepted answer? – Gordon Linoff Jul 09 '20 at 11:21