Removing Duplicate Rows Issue

I'm trying to remove duplicate rows from a table. tableA has 2 cols. acctid
and col1 When I run:
select count(distinct accid) from tableA
I get 10 as a result(I guess there are 10 distinct rows in the table,
correct?), but when I run:
select distinct acctid, col2 into #temp from tableA
(those are the only 2 columns in the table) I get 11 as the result, How
come? Shouldn't it say 10 rows affexted and not 11? any ideas?
thanks
Can you show us the structure of tableA, and the INSERT statements required
to populate it with these 10 or 11 rows?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> I'm trying to remove duplicate rows from a table. tableA has 2 cols.
acctid
> and col1 When I run:
> select count(distinct accid) from tableA
> I get 10 as a result(I guess there are 10 distinct rows in the table,
> correct?), but when I run:
> select distinct acctid, col2 into #temp from tableA
> (those are the only 2 columns in the table) I get 11 as the result, How
> come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> thanks
|||tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
when I do select count(distinct acctid) from tableA I get 10 rows as the
result, then when I select distinct aactid, col1 into #temp from tablea I get
11 rows, Why is this the case? Thank you
"Aaron [SQL Server MVP]" wrote:

> Can you show us the structure of tableA, and the INSERT statements required
> to populate it with these 10 or 11 rows?
> http://www.aspfaq.com/5006
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> acctid
>
>
|||Because you added a column to your distinct clause in the second query.
select distinct aactid, col1 from tablea
vs
select distinct aactid from tablea
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> when I do select count(distinct acctid) from tableA I get 10 rows as the
> result, then when I select distinct aactid, col1 into #temp from tablea I
get[vbcol=seagreen]
> 11 rows, Why is this the case? Thank you
> "Aaron [SQL Server MVP]" wrote:
required[vbcol=seagreen]
How[vbcol=seagreen]
|||that was an example, what I'm really trying to do is remove duplicate by
these statements
select distinct acctid, col1 into #temp from tableA
truncate table tableA
insert into tableA select * from #temp
the logic is in the previous posts!
"Jeff Dillon" wrote:

> Because you added a column to your distinct clause in the second query.
> select distinct aactid, col1 from tablea
> vs
> select distinct aactid from tablea
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> get
> required
> How
>
>
|||Yes, your logic is wrong, per my response.
You are aware that distinct acctid, col1 means take (acctid, col1) together,
and find all distinct rows. Distinct doesn't only apply to the first column
before your comma
To find duplicates:
select count(id), id from a
group by id
having count(id) > 1
Jeff
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:2B455174-0A8A-47E3-BB71-AA26CBE37D0E@.microsoft.com...[vbcol=seagreen]
> that was an example, what I'm really trying to do is remove duplicate by
> these statements
> select distinct acctid, col1 into #temp from tableA
> truncate table tableA
> insert into tableA select * from #temp
> the logic is in the previous posts!
> "Jeff Dillon" wrote:
data,[vbcol=seagreen]
the[vbcol=seagreen]
tablea I[vbcol=seagreen]
cols.[vbcol=seagreen]
table,[vbcol=seagreen]
result,[vbcol=seagreen]