Page 1 of 1
SELECT DISTINCT
Posted: Mon Feb 11, 2008 5:23 pm
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?
Re: SELECT DISTINCT
Posted: Mon Feb 11, 2008 5:45 pm
by Benjamin
I have done this in a while, I think your looking for Group By.
Re: SELECT DISTINCT
Posted: Mon Feb 11, 2008 6:38 pm
by John Cartwright
astions wrote:I have done this in a while, I think your looking for Group By.
Agreed
Re: SELECT DISTINCT
Posted: Mon Feb 11, 2008 7:11 pm
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...

Re: SELECT DISTINCT
Posted: Mon Feb 11, 2008 7:24 pm
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

Re: SELECT DISTINCT
Posted: Mon Feb 11, 2008 7:38 pm
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
Don't ask...
Anyways, I'm thinking that DISTINCT works just as you say...not binding to only a single field
Re: SELECT DISTINCT
Posted: Mon Feb 11, 2008 10:10 pm
by Festy
Does the following not work? -
Code: Select all
SELECT email,fname FROM table GROUP BY email
Re: SELECT DISTINCT
Posted: Mon Feb 11, 2008 10:48 pm
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