I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
> > I have a query which returns the following results. What i want to do
> > is remove any rows where col1 does not appear in col2. e.g. "107". As
> > well as this i want to remove any rows that are affected by this, e.g.
> > "109" as once 107 goes, 109 does not appear in col2.
> > Thanks in advance.
> > col1 col2
> > 1 108
> > 1 112
> > 1 115
> > 1 116
> > 1 117
> > 1 118
> > 107 109
> > 108 114
> > 109 110
> > 110 111
> > 112 113
> > 118 119
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan
Showing posts with label col1. Show all posts
Showing posts with label col1. Show all posts
Monday, March 12, 2012
Removing rows from a result
I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1108
1112
1115
1116
1117
1118
107109
108114
109110
110111
112113
118119
On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp
|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1108
1112
1115
1116
1117
1118
107109
108114
109110
110111
112113
118119
On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp
|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan
Removing rows from a result
I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select
1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select
1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan
Wednesday, March 7, 2012
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]
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]
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?
thanksCan 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...
> > 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
>
>|||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
> 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...
> > > 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
> >
> >
> >|||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...
> > 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...
> > > > 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
> > >
> > >
> > >
>
>|||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...
> 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...
> > > 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...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >
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?
thanksCan 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...
> > 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
>
>|||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
> 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...
> > > 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
> >
> >
> >|||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...
> > 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...
> > > > 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
> > >
> > >
> > >
>
>|||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...
> 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...
> > > 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...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >
Subscribe to:
Posts (Atom)