select distinct...again

Got it...

Thanks for your help! I just thought there was a way to work around it.
Thanks for the ideas

The only thing I could think of is a little convoluted but it works.
- create temp table
- insert distinct emails
- update other fields

This could be a stored procedure. If you only perform this once in a
while or the tables are small this sould not be a problem.


if object_id('TempDb..#tt') is not null drop table #tt

-- create status summary table
create table [#tt] (
[email] [varchar] (50) NOT NULL,
[last_name] [varchar] (50),
[first_name] [varchar] (50),
CONSTRAINT PK_em PRIMARY KEY (email)
)

insert into #tt (email)
select distinct email
from members
where email is not null


update t
set t.last_name = m.last_name, t.first_name = m.first_name
from #tt t, members m
where t.email = m.email



select * from #tt
order by email

Art wrote:
> Philo wrote:
>> for MSSQL: The DISTINCT keyword is optional with SUM, AVG, and COUNT.
>> When DISTINCT is used, duplicate values are eliminated before the sum,
>> average, or count is calculated.
>>
>> Basically it eleminiates duplicates. That does not seem to be what you
>> are doing. By distinct do you mean none null emails addresses? If so
>> than something like "where email is not null" is what you want.
>>
>
> No, none of the emails are null and that's not what I want.
>
> If I just do
>
> SELECT DISTINCT EMail
> FROM table
>
> then I get distinct. If I add another column, I think the "distinct
> checks that row too and because the rows with the same email are not the
> same, it pulls it in. How do I tell SQL not to display duplicates and
> list other columns?

The reason its not working is because its looking for unique rows, not
columns. When you put in the names its looking for a whole row which is
unique. When you just include EMail it can find the unique values, but
as soon as you put the others in, it is looking for unique combinations.

It looks like you have different names with the different emails,
otherwise it should work. You need to clean the data and remove the
duplicates first.

Steve

Art wrote:
> Philo wrote:
>> for MSSQL: The DISTINCT keyword is optional with SUM, AVG, and COUNT.
>> When DISTINCT is used, duplicate values are eliminated before the sum,
>> average, or count is calculated.
>>
>> Basically it eleminiates duplicates. That does not seem to be what you
>> are doing. By distinct do you mean none null emails addresses? If so
>> than something like "where email is not null" is what you want.
>>
>
> No, none of the emails are null and that's not what I want.
>
> If I just do
>
> SELECT DISTINCT EMail
> FROM table
>
> then I get distinct. If I add another column, I think the "distinct
> checks that row too and because the rows with the same email are not the
> same, it pulls it in. How do I tell SQL not to display duplicates and
> list other columns?

The reason its not working is because its looking for unique rows, not
columns. When you put in the names its looking for a whole row which is
unique. When you just include EMail it can find the unique values, but
as soon as you put the others in, it is looking for unique combinations.

It looks like you have different names with the different emails,
otherwise it should work. You need to clean the data and remove the
duplicates first.

Steve

Art wrote:
> This pulls in the same thing. If I just pull in email, it works but as
> soon as i list fname, lname...the "distinct" fails.
>>
>> Maybe try:
>>
>> SELECT DISTINCT TOP 100 email, FName, LName
>> FROM table1
>> WHERE MailList = 1
>>
>> Steve

not sure if this will work

SELECT TOP 100 FName, LName, DISTINCT email
FROM table1
WHERE MailList = 1

Philo wrote:
> for MSSQL: The DISTINCT keyword is optional with SUM, AVG, and COUNT.
> When DISTINCT is used, duplicate values are eliminated before the sum,
> average, or count is calculated.
>
> Basically it eleminiates duplicates. That does not seem to be what you
> are doing. By distinct do you mean none null emails addresses? If so
> than something like "where email is not null" is what you want.
>

No, none of the emails are null and that's not what I want.

If I just do

SELECT DISTINCT EMail
FROM table

then I get distinct. If I add another column, I think the "distinct
checks that row too and because the rows with the same email are not the
same, it pulls it in. How do I tell SQL not to display duplicates and
list other columns?

This pulls in the same thing. If I just pull in email, it works but as
soon as i list fname, lname...the "distinct" fails.
>
> Maybe try:
>
> SELECT DISTINCT TOP 100 email, FName, LName
> FROM table1
> WHERE MailList = 1
>
> Steve

I'll try what you suggested and get back to you. Thanks

In the meantime, what I want is to have unique rows. In other words,
there may be duplicates in other columns but in the email column, I want
only one if there are duplicates.

In the database, there may be duplicate emails. If there are duplicates,
I want only one of these duplicates to show up. Right now, I'm getting
results but I'm getting results with duplicate email address.

Philo wrote:
>> It sounds like he wants to email all the unique email addresses, so
>> DISTINCT should work as it avoids duplicates.
>
>
> But then his other query should have worked. Right?

Possibly, it could have been a syntax issue, which is why I wrote it
differently...I used my Gurus Guide to Transact-SQL to check. Most of
the examples I find tend to use DISTINCT in an aggregate function.

Steve

> It sounds like he wants to email all the unique email addresses, so
> DISTINCT should work as it avoids duplicates.


But then his other query should have worked. Right?
>
> Maybe try:
>
> SELECT DISTINCT TOP 100 email, FName, LName
> FROM table1
> WHERE MailList = 1
>
> Steve



--