Selecting distinct records from table (1 Viewer)

[Harris@Z]

Hi, I hope someone can assist.

I have a table with the following fields: Contact_ID, Company_ID and EmailAddress.

The situation is that the same email address can be associated with 1 or more companies.
Therefore the Contact_ID and Company_ID will differ from the same email address.

For example
Contact_ID Company_ID EmailAddress
1212 456 Joe@example.com
1453 597 Joe@example.com
6236 456 Fred@example.com
7235 839 Joe@example.com

I would like to design a query that shows only 1 distinct Joe@example.com email address, associated with either the lowest or highest Contact_ID (I do not have a preference)

So the result may be:
Contact_ID Company_ID EmailAddress
1212 456 Joe@example.com
6236 456 Fred@example.com

Is this possible?

Thanks!

 

[plog]

You would need a subquery to do this. That subquery would find the lowest Contact_ID for an email:

SELECT EmailAddress, MIN(Contact_ID) AS LowestContact
FROM YourTableNameHere
GROUP BY EmailAddress

Save that as 'sub1', then build another query using it and YourTableNameHere. JOIN them via Contact_ID to LowestContact and bring in all the fields from YourTableNameHere.

 

[Harris@Z]

Thank you, worked like a charm!
I found similar ideas on other sites but simply could not get them to work.
Your explanation was spot on, and I was able to translate this within seconds.
Much appreciated.
H

 

[isladogs]

As an alternative this can also be done without a subquery.
To do so use an aggregate query with a self join

SELECT First(Table1.Contact_ID) AS FirstOfContact_ID, First(Table1.Company_ID) AS FirstOfCompany_ID, Table1_1.EmailAddress
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.EmailAddress = Table1_1.EmailAddress
GROUP BY Table1_1.EmailAddress;

 

[arnelgp]

overthinking i supposed.

 

[Harris@Z]

Thank you, a super solution. Makes it easier to incorporate with my the intention I had in mind!
Much appreciated

 

[isladogs]

You're welcome. Both methods work equally well

 

[Pat Hartman]

SELECT First(Table1.Comtact_ID) AS FirstOfComtact_ID, First(Table1.Company_ID) AS FirstOfCompany_ID, Table1_1.EmailAddress FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.EmailAddress = Table1_1.EmailAddress GROUP BY Table1_1.EmailAddress;

Why the self join? Why not?

SELECT First(Table1.Contact_ID) AS FirstOfContact_ID, First(Table1.Company_ID) AS FirstOfCompany_ID, Table1_1.EmailAddress FROM Table1
Group by Table1_1.EmailAddress
Order by Table1_EmailAddress, Table1.Contact_ID;

 

[isladogs]

It was early morning when I responded. Not fully awake!
Yes it can be done without a self-join. That's even better BUT the query SQL in post #8 gave this error as the sort fields aren't in the query

Remove the sort & it works:

SELECT First(Table1.Contact_ID) AS FirstOfContact_ID, First(Table1.Company_ID) AS FirstOfCompany_ID, Table1.EmailAddress
FROM Table1
GROUP BY Table1.EmailAddress;

OR sort by the aggregate fields

SELECT First(Table1.Contact_ID) AS FirstOfContact_ID, First(Table1.Company_ID) AS FirstOfCompany_ID, Table1.EmailAddress
FROM Table1
GROUP BY Table1.EmailAddress
ORDER BY First(Table1.Contact_ID), First(Table1.Company_ID);

 

[plog]

Smells like another classic AccessWorld 'I-Can-Outdo-Your-Solution-To-A-Trivial-Issue contest. Everyone, please submit your entries below. Remember the fact that the original poster has gotten what they needed is now totally irrelevant to this thread. Bonus points for VBA, complicated & obfuscated SQL, ad hominin attacks and crapping on others submissions.

 

[cheekybuddha]

You could copy and paste the data in to Excel ...

 

[ebs17]

If a solution is voted on here, my vote goes to @plog , although I would combine everything in one query.

I'm regularly suspicious of using First or Last because they depend on an order that you often can't control.
Above you can see again that sorting only takes place after grouping and aggregating. Sorting comes too late for this process. But you can't take the order of the original table for granted either. When developing queries, e.g. when grouping, indexes can be used that temporarily create completely different sequences.

 

[isladogs]

Smells like another classic AccessWorld 'I-Can-Outdo-Your-Solution-To-A-Trivial-Issue contest. Everyone, please submit your entries below. Remember the fact that the original poster has gotten what they needed is now totally irrelevant to this thread. Bonus points for VBA, complicated & obfuscated SQL, ad hominin attacks and crapping on others submissions.

You're entitled to your opinion though I see nothing in what I wrote that justifies your comments.
Nowhere did I suggest my approach was better than yours.

Post #4

As an alternative ...

Post #7

You're welcome. Both methods work equally well

The OP and others can decide for themselves which method they prefer.

 

[plog]

I didn't intend to suggest that you suggested that your approach was better than mine.

 

[Pat Hartman]

I corrected what I thought were typos in the query since "comtact" seems to have been a misspelling.