Records do not come out DISTINCT

[Bik]

I have have a column where there would be some duplicate records, but I have tried using the IN list, DISTINCT, and GROUP BY, and it does not give me distinct record set.

However DISTINCT and GROUP BY will work as long as my columns to display remain only one, but the minute I add more columns to my SELECT statement it does not make them distinct anymore.

Example 1 works, but Example 2 does NOT. In Example 2, I have added more columns otherwise it identical to Example 1. In Example 3, I was using the IN list, but the results of Example 3 is identical to Example 2 !!

EXAMPLE 1:
SELECT DISTINCT email_address AS Email
FROM email_address
WHERE Send = 'y' and (DATEDIFF(DAY, Sent, getdate()) > 30)

EXAMPLE 2:
SELECT distinct email_address AS Email, email_address_ID AS ID, Sent
FROM email_address
WHERE Send = 'y' and (DATEDIFF(DAY, Sent, getdate()) > 30)

EXAMPLE 3:
SELECT email_address_ID AS ID, email_address AS Email, Sent
FROM email_address
WHERE email_address IN (SELECT DISTINCT email_address FROM email_address
WHERE Send = 'y' and (DATEDIFF(DAY, Sent, getdate()) > 30))

[sduggan]

Bik,
Without seeing what is in the dataset I can not tell you definitively why Example 2 is not working, but I guess it could be the fact that you are including a datetime field in the return columns. Even if it is a smalldatetime, it is down to the minute, so if the user sent two emails one after another there is a strong likelihood that the emails will be in different minutes.

I assume you are trying to establish the last activity of an email_id/address?

If so then perhaps you could try grouping and using aggregate functions:

Select email_address, email_id, max(sent) as sent
from tblEmailAddress
where Send = 'y' and (DATEDIFF(DAY, Sent, getdate()) > 30))
group by email_address, email_id

Cheers sduggan

[Markus]

What do you mean with not working. Does example 2 returns an error or just not the result set you expect.

Quite honestly I don't think the DISTINCT keyword is of any use since one of your output columns is an ID field.

[pithhelmet]

I've had this happen before and was told that because your asking for multiple fields to be returned - that is tosses DISTINCT out the window -

he explained to me that it evals ALL the fields in the select, and disregards the single distinct statement.... i dunno know if he is right or not - but shure enough, with multiple selected items, and the distinct statement, it will fail.


good luck