CODEWITHSUNDEEP
sqlduplicates
user1659537
user1659537

Reputation: 11

SQL remove duplicates if more 2

I have a database that needs filtering.

The database contains rows of employees at various companys who have signed up to a newsletter. I don't wish to send the newsletter to each one, I only want to send it to 2 from each company. what would my SQL be to remove the duplicates if more than 2 from 1 company listed?

Thanks for any help.

Upvotes: 1

Views: 142

Answers (2)

Hunter
Hunter

Reputation: 890

May be this will help you...

DELETE FROM newsletter nl
WHERE ID NOT IN
(
    select id from newsletter ne where ne.comp_name=hl.comp_name limit 2
)

I am using table name as examples just replace it with your original name.

Upvotes: 2

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

Try this query. It is for Sql Server database.

select * from Employees e
where id in (select top 2 id from Employees ie where e.company = ie.company)

Please replace the table and field names as the exact structure of table is not known.

Upvotes: 1

Related Questions