CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 71

MySQL DISTINCT one column select all

I've been checking stackoverflow and found some similar questions but non is 100% confirmed I think.

I'm trying to select * records from MySQL db either * or firstname, lastname ... + kind of DISTINCT email.

I have some duplicated users in the database but I want to select them only once. Apart of distinct email I also need all other fields.

I've SEEN something like SELECT DISTINCT EMAIL, FIRSTNAME, LASTNAME FROM USERS. Is this accurate? I've received different amount of rows if I only distinct email, different if DISTINCT EMAIL, FIRSTNAME, different when DISTINCT EMAIL, FIRSTNAME, LASTNAME ect.

I suppose result should be the same for DISTINCT(EMAIL) as for DISTINCT EMAIL, FIRSTNAME, LASTNAME,... if that's correct query.

Do you have any other ways of selecting those rows in MySQL?

Upvotes: 0

Views: 1108

Answers (2)

Rahul
Rahul

Reputation: 77896

Yes it's correct; you should use DISTINCT operator to get the unique data but uniqueness depends on combination.

DISTINCT EMAIL will get all the unique EMAIL but

DISTINCT FIRSTNAME, LASTNAME will get you the distinct combination of FIRSTNAME and LASTNAME. Like Adam Miller and Miller Adam may not be duplicate.

Similarly, DISTINCT EMAIL, FIRSTNAME, LASTNAME will get you the combination of EMAIL, FIRSTNAME, LASTNAME. Hence you are observing different result counts.

Upvotes: 1

webzar
webzar

Reputation: 114

well GROUP BY can solve your problem. Here is the sample code.

SELECT * FROM USERS GROUP BY `EMAIL`

This will group all the duplicate entries of emails.

Hope it helps you.

Upvotes: 0

Related Questions