Remove duplicate in SQL with specfic column

Question

My requirement is to remove duplicate in sql query with specific column (i.e. similar to remove duplicate option in MS Excel.)

Eg: my query o/p is with 8 column but duplicate to be removed with 3 columns. With the following 8 column

date,name, dept_name,city,phne_no,country,gender,age

i need to remove duplicate w.r.t

name, city, phne_no

Is this possible in sql? Please note that i do not have access to delete data in table hence i can only extract. Kindly help me out.

if you have two rows with the same `name`, `city`, and `phne_no`, which row do you keep? — pala_, Apr 20 '15 at 05:37
possible duplicate of [Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Maciej Los, Apr 20 '15 at 06:15

jarlh · Accepted Answer · 2015-05-22T10:45:55.557

For example you can delete a row if another row with the same name, city, and phne_no EXISTS, but with a newer date:

   delete from tablename t1 where exists (select 1 from tablename t2
                                           where t1.name = t2.name
                                             and t1.city = t2.city
                                             and t1.phne_no = t2.phne_no
                                             and t2.date > t1.date)

Edit: If you just want to select the newer rows, use NOT EXISTS to find rows without any newer "duplicates":

select *
from tablename t1
where not exists (select 1 from tablename t2
                  where t1.name = t2.name
                    and t1.city = t2.city
                    and t1.phne_no = t2.phne_no
                    and t2.date > t1.date)

I can only extract data through a query and i dont have access to delete data — Raaj, Apr 20 '15 at 08:00

Remove duplicate in SQL with specfic column

1 Answers1