CODEWITHSUNDEEP
mysqlsql
zod
zod

Reputation: 12437

SQL - find the duplicates

select * from table1 where destination in  ('chi','usa','ind')

Ideally my result set should be 3 rows. one row for each destination.

But in my result there are duplicates. 

chi
usa
ind
usa

I want to find which destination is duplicated. I have hundreds of destination and i want to delete the duplicate records. I am not looking for distinct. Am looking for the duplicated ones.

Upvotes: 0

Views: 168

Answers (3)

dfb
dfb

Reputation: 13289

Use group by and having instead of distinct

select destination from table1 
where destination in  ('chi','usa','ind')
group by destination 
having count(*)>1

If you want to delete these and retain one, it gets a little messy. This is probably the shortest way, but its a bit of a hack. 

delete from destination where id not in (
    select max(id) from table1
      where destination in  ('chi','usa','ind')
      group by destination 
    )

Upvotes: 7

Brian Patterson
Brian Patterson

Reputation: 1625

Add group by at the end ... should give you two columns, on is the country, the other is how many times it was in the list.

So ..

select * from table1 where destination in  ('chi','usa','ind') group by destination

Hope this helps

Upvotes: 0

James Hill
James Hill

Reputation: 61872

This will give you a count of duplicates, ordered by number of dupes desc:

SELECT CountryAbbr,
       COUNT(*) AS DuplicateCount
FROM   YourTable
GROUP BY CountryAbbr
HAVING COUNT(*) > 1
Order By 2 DESC

Upvotes: 1

Related Questions