CODEWITHSUNDEEP
phpmysql
user3855851
user3855851

Reputation: 175

MYSQL Query to ignore every duplicate

I have a table which has duplicate rows. I want to do a select which will remove ALL duplicate rows.

I know i can do a GROUP BY but that will still show a single row for the duplicate. What i want is that all the duplicates should be ignored.

E.g

I have a table named "users" which has a column named "username" which have the following values:

  1. John
  2. Sam
  3. Reyaan
  4. Reyaan
  5. Sam.

So when the select query finishes, it should only show the row with "John" and ignore everything else because Sam and Reyaan were duplicated.

Thanks.

Upvotes: 0

Views: 521

Answers (4)

jasinth premkumar
jasinth premkumar

Reputation: 1413

you can use this query to fetch record 

SELECT name FROM people GROUP BY name HAVING COUNT(name) = 1;

Upvotes: -1

jun drie
jun drie

Reputation: 872

You can use NOT IN function

SELECT username FROM users WHERE username not in (select username from users group by username having count(username)>1)

Upvotes: 1

Tomer Shay
Tomer Shay

Reputation: 801

You can use the HAVING clause to make sure there is only one row returned for each of the groups. That way, you'll make sure only names that have specifically 1 instance (not zero, not more than 1) will be returned.

Example:

SELECT 
    name
FROM
    people
GROUP BY name
HAVING COUNT(name) = 1;

Upvotes: 3

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use count and check the result of count in having clause

select username
from users
group by username
having count(*) = 1

Demo

Upvotes: 6

Related Questions