SELECT DISTINCT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

SELECT DISTINCT

Post by alex.barylski »

I'm using the above to remove duplicate email addresses but now I need to return other details along with the query:

Code: Select all

SELECT DISTINCT email FROM table WHERE ...
This works great but only returns the emails, I need to return a few other fields...how is this done while keeping the records unique according to only that one field, emails???

Code: Select all

SELECT DISTINCT email, fname FROM table WHERE ...
THe problem with the above approach, is I don't want the name to be distinct...just the email. It's very likely that two people share the same name, but obviously not the same email address, in which case there should be two records not one...

Ideas?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: SELECT DISTINCT

Post by Benjamin »

I have done this in a while, I think your looking for Group By.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: SELECT DISTINCT

Post by John Cartwright »

astions wrote:I have done this in a while, I think your looking for Group By.
Agreed
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: SELECT DISTINCT

Post by alex.barylski »

GROUP BY...I think I used that already and for some reason it didn't work...

What does DISTINCT do exactly then and/or how does it differ from GROUP BY?

I thought DISTINCT essentially prevented from having duplicates based on the field you makr as DISTINCT?

I added other fields to my query and they are returned fine in the resultset, so it looks like DISTINCT only binds to the first field...

I have this:

Code: Select all

SELECT DISTINCT person.email, person.id, person.name WHERE ...
Sometimes, the same person (email or id) is returned because they are associated with multiple lists (which is what I want DISTINCT to do - remove the duplicates). The problem I fear, is that there may be someone with the same name as someone else and DISTINCT will remove them or rather merge that as a duplicate as well...

Although I'm not sure that would make sense...I think it should essentially just prevent duplicates of people who have the same fields all specified...

So using DISTINCT only on my pkid field should suffice as only one user will ever have that value... :banghead:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: SELECT DISTINCT

Post by John Cartwright »

Hockey wrote: I added other fields to my query and they are returned fine in the resultset, so it looks like DISTINCT only binds to the first field...
Actually I'm fairly sure it takes into account the uniqueness of all the columns specified. If it's possible that the email address can show up more than once in the result set then you want to use DISTINCT and specify both the email and another unique identifier to your mailing list.

GROUP BY will guarantee only a single row containing that particular value is returned in the resultset.

It's also been awhile since I've buggered with DISTINCT, so feel free to prove me wrong :)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: SELECT DISTINCT

Post by alex.barylski »

It's also been awhile since I've buggered with DISTINCT, so feel free to prove me wrong
Ok...PI R *actually* round NOT squared :P

Don't ask... :)

Anyways, I'm thinking that DISTINCT works just as you say...not binding to only a single field
Festy
Forum Commoner
Posts: 28
Joined: Wed Jan 30, 2008 10:01 pm

Re: SELECT DISTINCT

Post by Festy »

Does the following not work? -

Code: Select all

 
SELECT email,fname FROM table GROUP BY email
 
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: SELECT DISTINCT

Post by Christopher »

This is one of the few weird things about SQL which is usually pretty straightforward.

DISTINCT and GROUP BY are the same when only selecting columns:

Code: Select all

SELECT DISTINCT email, fname FROM table WHERE ...
does the same thing as

Code: Select all

SELECT email, fname FROM table WHERE ... GROUP BY email, fname
However (and you knew there was a "however" ;)) GROUP BY works with there are also functions present.

This does not work as expected:

Code: Select all

SELECT DISTINCT email, fname, COUNT(*) FROM table WHERE ...
But this does the right thing:

Code: Select all

SELECT email, fname, COUNT(*) FROM table WHERE ... GROUP BY email, fname
(#10850)
Post Reply