select DISTINCT email but also get the ID? 1

[pha2er]

hi there,

Wonder if anyone can help me here, I have a database with about 20 columns, but I only want to select DISTINCT email column and the ID to go along with that column.

There are lots of duplicate email addresses with different information, but I need to get DISTINCT email's and the corresponding ID.

Anyone know what I'm talking about?

Cheers

 

[bperry]

When you have duplicate e-mail addresses, will each of these duplicates have the same ID value, or have different IDs?

 

[tlbroadbent]

The following will return unique Email and ID combinations. Is that what you need?

Select Email, ID
From Table
Group By Email, ID Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[pha2er]

bperry - they all have different ID's

tlbroadbent - thats a step closer but, here's an example of a few rows from the table:

ID EMail Name
21 jim@nowhere.com Jim Bowen
22 man@overhere.com Man Well
23 jim@nowhere.com Jim Bowen
24 admin@booyaa.com Pierre La'Cross
25 woman@overthere.com Wu Man
26 jim@nowhere.com Jim Bowen

So there's 3 Jim Bowen's in there, all with the same e-mail, but different ID's, so what I need to do is only bring back one of those Jim Bowen's, it doesnt matter which one, and the corresponding ID and name.

I can do it with ASP, just by running 2 SQL Queries, but it would be easier if there was a way to do it with one SQL query.

Thanks.

 

[ldandy]

You can do it using a subquery (I do not know how slow will it be). Let's say the table is called CONTACTS. Then the query should be:

SELECT CONTACTS.EMAIL, CONTACTS.ID, CONTACTS.NAME FROM CONTACTS WHERE CONTACTS.ID = (SELECT max(CONTACTS.ID) FROM CONTACTS AS CONTACTS_1 WHERE CONTACTS_1.EMAIL = CONTACTS.EMAIL)

Danny.

 

[ldandy]

There is a little typo in my previous sql statement. Here is the correct version:

SELECT CONTACTS.EMAIL, CONTACTS.ID, CONTACTS.NAME FROM CONTACTS WHERE CONTACTS.ID = (SELECT max(CONTACTS_1.ID) FROM CONTACTS AS CONTACTS_1 WHERE CONTACTS_1.EMAIL = CONTACTS.EMAIL)

Danny.

 

[arrowhouse]

You could also use -
select max(CONTACTS.ID), CONTACTS.EMAIL from
CONTACTS

where ...(whatever your conditions are)...

group by CONTACTS.EMAIL

 

[pha2er]

Cheers ldandy, you da man, that works a treat.