I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:
> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:
>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to
>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label duplicate. Show all posts
Showing posts with label duplicate. Show all posts
Wednesday, March 7, 2012
Removing duplicates
Can somebody help me by telling the best way to remove duplicate rows from a table.You can query a table with SELECT DISTINCT to cause duplicate data to be omitted.
You could query the table in this way into a temporary table, empty the old table and repopulate it from the temporary.|||This depends on the structure of your table and what rows will be deleted and which will be kept. Are the entire rows identical (no unique identifier) ? How many records are in the table ?
You could query the table in this way into a temporary table, empty the old table and repopulate it from the temporary.|||This depends on the structure of your table and what rows will be deleted and which will be kept. Are the entire rows identical (no unique identifier) ? How many records are in the table ?
Removing Duplicate Values in Filter List...
Hello!
I am pulling two values from a field when the report is first run. The
value can either be ACTIVE or DISABLED. This is being accessed in a
parameter and I would like to be able to filter my current report by
selecting either ACTIVE or DISABLED records. The catch is that I need to do
this without taking a trip back to the server.
I could hardcode the two values in there, however that would require the
report to re-render each time the filter is applied. Can I have two distinct
values in my filter and have this applied at the time of the first rendering?
Right now I get all values for my status_cd column which is a bunch of
DISABLED and ACTIVE values. I just need two. Let me know what you think.
Thanks!Just a short clarification. After I wrote all that I realized that I needed
to explain a little more...
We are pulling in the values for the filter from a dataset. The
Active/Disabled example isn't all that great because I can hardcode the
values for that to get it to work. However, the functionality we really need
is as follows...
We have users that are associated with clients. So multiple users have the
same client. In this filter drop-down when it populates from the dataset it
is populating the same client multiple times based upon how many users are
associated with that client. All we need is each client a single time. We
can do this by creating another dataset, but we would like to do it with the
current dataset instead of making a trip back to the server. Your help is
appreciated. Thanks!
"jrennard" wrote:
> Hello!
> I am pulling two values from a field when the report is first run. The
> value can either be ACTIVE or DISABLED. This is being accessed in a
> parameter and I would like to be able to filter my current report by
> selecting either ACTIVE or DISABLED records. The catch is that I need to do
> this without taking a trip back to the server.
> I could hardcode the two values in there, however that would require the
> report to re-render each time the filter is applied. Can I have two distinct
> values in my filter and have this applied at the time of the first rendering?
> Right now I get all values for my status_cd column which is a bunch of
> DISABLED and ACTIVE values. I just need two. Let me know what you think.
> Thanks!
I am pulling two values from a field when the report is first run. The
value can either be ACTIVE or DISABLED. This is being accessed in a
parameter and I would like to be able to filter my current report by
selecting either ACTIVE or DISABLED records. The catch is that I need to do
this without taking a trip back to the server.
I could hardcode the two values in there, however that would require the
report to re-render each time the filter is applied. Can I have two distinct
values in my filter and have this applied at the time of the first rendering?
Right now I get all values for my status_cd column which is a bunch of
DISABLED and ACTIVE values. I just need two. Let me know what you think.
Thanks!Just a short clarification. After I wrote all that I realized that I needed
to explain a little more...
We are pulling in the values for the filter from a dataset. The
Active/Disabled example isn't all that great because I can hardcode the
values for that to get it to work. However, the functionality we really need
is as follows...
We have users that are associated with clients. So multiple users have the
same client. In this filter drop-down when it populates from the dataset it
is populating the same client multiple times based upon how many users are
associated with that client. All we need is each client a single time. We
can do this by creating another dataset, but we would like to do it with the
current dataset instead of making a trip back to the server. Your help is
appreciated. Thanks!
"jrennard" wrote:
> Hello!
> I am pulling two values from a field when the report is first run. The
> value can either be ACTIVE or DISABLED. This is being accessed in a
> parameter and I would like to be able to filter my current report by
> selecting either ACTIVE or DISABLED records. The catch is that I need to do
> this without taking a trip back to the server.
> I could hardcode the two values in there, however that would require the
> report to re-render each time the filter is applied. Can I have two distinct
> values in my filter and have this applied at the time of the first rendering?
> Right now I get all values for my status_cd column which is a bunch of
> DISABLED and ACTIVE values. I just need two. Let me know what you think.
> Thanks!
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
> > > >
> > > >
> > > >
> >
> >
> >
removing duplicate rows
Hi,
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggreate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
Smithanot very efficient - and prone to possible truncation of col2 -
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggreate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
Smithanot very efficient - and prone to possible truncation of col2 -
select distinct col1, cast(col2 as varchar(8000)) from test|||Thanks. I need the output to be with the same datatype, since I need to create temp tables using the selected data(using SELECT INTO)|||again not very efficient:
select temp.col1, cast(temp.newcol2 as text) col2
into newtable
from
(select distinct col1, cast(col2 as varchar(8000)) newcol2 from test) temp
removing duplicate rows
I am not able to figure out a script that removes duplicates if for the same
SSNs, the startdate and a code is the same.
sample data
SSN startdate code
123456789 11-12-1980 2540
456789123 14-5-1965 1236
123456789 02-09-1980 7456
789456132 06-07-1950 2589
123456789 11-12-1980 2540
What I want deleted is here is :
123456789 11-12-1980 2540 (just one of the two records)
Any help would be great
thanksAs long as you don=B4t have any identifier which can differ between the
rows there are onl workarounds for that:
1=2E Include a identifier to differ (like an identity column), then use
the follwing code:
DELETE YourTable FROM YourTable T
INNER JOIN
(
SELECT identifiercolumn from YourTable
GROUP BY SSN,startdate,code
HAVING COUNT(*) > 1
) SuQUery
ON SubQuery.identifiercolumn =3D T.identifiercolumn
2=2E (Preferable one) Copy one of the duples to a temp table, delete all
duplicates and reinsert the data from the temp table.
3=2E Avoid duplicates !
HTH, Jens Suessmeyer.|||Jens
There is a sequence number in the table that is unique. But It will not
matter which one we delete I would prefer the number that has a greater valu
e
(as in the seqnumber). Also I am pretty new to programming in SQL so if
possible can u actually show me how to crerate an identifier Copy one of the
duples to a temp table, delete all duplicates and reinsert the data from the
temp table.And avoid duplicates !
Thanks for your help
Amit
"Jens" wrote:
> As long as you don′t have any identifier which can differ between the
> rows there are onl workarounds for that:
> 1. Include a identifier to differ (like an identity column), then use
> the follwing code:
> DELETE YourTable FROM YourTable T
> INNER JOIN
> (
> SELECT identifiercolumn from YourTable
> GROUP BY SSN,startdate,code
> HAVING COUNT(*) > 1
> ) SuQUery
> ON SubQuery.identifiercolumn = T.identifiercolumn
> 2. (Preferable one) Copy one of the duples to a temp table, delete all
> duplicates and reinsert the data from the temp table.
> 3. Avoid duplicates !
> HTH, Jens Suessmeyer.
>|||Hi There,
Having a table with all column values identical for two or more rows
means that there are no constraints . Are you trying to manage history
and usable data in the same table?
I Think this query might help you.
Delete from YourTableName Where RowID NOT IN (Select MIN(ROWID) From
YourTableName Group By FieldList,......)
The group by will have all the fields except ROWID (or Unique
identifier)
I still say Aviod duplicates and have constraints on the table . Manage
history and usable data in different table (if this is the case).
With Warm regards
Jatinder SIngh
SSNs, the startdate and a code is the same.
sample data
SSN startdate code
123456789 11-12-1980 2540
456789123 14-5-1965 1236
123456789 02-09-1980 7456
789456132 06-07-1950 2589
123456789 11-12-1980 2540
What I want deleted is here is :
123456789 11-12-1980 2540 (just one of the two records)
Any help would be great
thanksAs long as you don=B4t have any identifier which can differ between the
rows there are onl workarounds for that:
1=2E Include a identifier to differ (like an identity column), then use
the follwing code:
DELETE YourTable FROM YourTable T
INNER JOIN
(
SELECT identifiercolumn from YourTable
GROUP BY SSN,startdate,code
HAVING COUNT(*) > 1
) SuQUery
ON SubQuery.identifiercolumn =3D T.identifiercolumn
2=2E (Preferable one) Copy one of the duples to a temp table, delete all
duplicates and reinsert the data from the temp table.
3=2E Avoid duplicates !
HTH, Jens Suessmeyer.|||Jens
There is a sequence number in the table that is unique. But It will not
matter which one we delete I would prefer the number that has a greater valu
e
(as in the seqnumber). Also I am pretty new to programming in SQL so if
possible can u actually show me how to crerate an identifier Copy one of the
duples to a temp table, delete all duplicates and reinsert the data from the
temp table.And avoid duplicates !
Thanks for your help
Amit
"Jens" wrote:
> As long as you don′t have any identifier which can differ between the
> rows there are onl workarounds for that:
> 1. Include a identifier to differ (like an identity column), then use
> the follwing code:
> DELETE YourTable FROM YourTable T
> INNER JOIN
> (
> SELECT identifiercolumn from YourTable
> GROUP BY SSN,startdate,code
> HAVING COUNT(*) > 1
> ) SuQUery
> ON SubQuery.identifiercolumn = T.identifiercolumn
> 2. (Preferable one) Copy one of the duples to a temp table, delete all
> duplicates and reinsert the data from the temp table.
> 3. Avoid duplicates !
> HTH, Jens Suessmeyer.
>|||Hi There,
Having a table with all column values identical for two or more rows
means that there are no constraints . Are you trying to manage history
and usable data in the same table?
I Think this query might help you.
Delete from YourTableName Where RowID NOT IN (Select MIN(ROWID) From
YourTableName Group By FieldList,......)
The group by will have all the fields except ROWID (or Unique
identifier)
I still say Aviod duplicates and have constraints on the table . Manage
history and usable data in different table (if this is the case).
With Warm regards
Jatinder SIngh
Removing duplicate rows
I need to remove duplicate rows that are not exactly
identical in all columns. Only if a row has three columns
that are identical to another, I want to remove it. In my
example I'm looking for Account_Number, Check_Number,
Check_Amount. Is there a Query that can do this?
LeeWithout seeing the DDL it's impossible to give a precise answer. If you have
a single-column primary key (Keycol in this example) and don't mind which of
the duplicate rows get removed:
DELETE FROM Sometable
WHERE keycol NOT IN
(SELECT MIN(keycol)
FROM Sometable
GROUP BY account_number, check_number, check_amount)
--
David Portas
--
Please reply only to the newsgroup
--
"Lee Surma" <lee@.honeycomb.net> wrote in message
news:018101c36b3c$f66dd9f0$a601280a@.phx.gbl...
> I need to remove duplicate rows that are not exactly
> identical in all columns. Only if a row has three columns
> that are identical to another, I want to remove it. In my
> example I'm looking for Account_Number, Check_Number,
> Check_Amount. Is there a Query that can do this?
> Lee
>
>|||This might help you:
http://www.sql-server-
performance.com/rd_delete_duplicates.asp
How big is the table?
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>|||More, this one from Microsoft:
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q1
39/4/44.asp&NoWebContent=1
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>
identical in all columns. Only if a row has three columns
that are identical to another, I want to remove it. In my
example I'm looking for Account_Number, Check_Number,
Check_Amount. Is there a Query that can do this?
LeeWithout seeing the DDL it's impossible to give a precise answer. If you have
a single-column primary key (Keycol in this example) and don't mind which of
the duplicate rows get removed:
DELETE FROM Sometable
WHERE keycol NOT IN
(SELECT MIN(keycol)
FROM Sometable
GROUP BY account_number, check_number, check_amount)
--
David Portas
--
Please reply only to the newsgroup
--
"Lee Surma" <lee@.honeycomb.net> wrote in message
news:018101c36b3c$f66dd9f0$a601280a@.phx.gbl...
> I need to remove duplicate rows that are not exactly
> identical in all columns. Only if a row has three columns
> that are identical to another, I want to remove it. In my
> example I'm looking for Account_Number, Check_Number,
> Check_Amount. Is there a Query that can do this?
> Lee
>
>|||This might help you:
http://www.sql-server-
performance.com/rd_delete_duplicates.asp
How big is the table?
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>|||More, this one from Microsoft:
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q1
39/4/44.asp&NoWebContent=1
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>
removing duplicate rows
Hi,
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
MiraJTry converting the columns to, for instance, varchar by using CAST() and then concatenating them to one string. Before going on with DISTINCT or whatever needed.|||I want the output to be in the same datatype. Hence casting will not solve my purpose.|||nabucco's suggestion works:
select cast(col2 as text) col2
from (
select distinct cast(col2 AS varchar(100)) col2 from test
) x
col2
--
abc
dbc|||nabucco's suggestion works:
select cast(col2 as text) col2
from (
select distinct cast(col2 AS varchar(100)) col2 from test
) x
col2
--
abc
dbc
How will u solve the issue if the text data is bigger than 8000 ?|||Just a thought ...
Can use substring and then concatenate every 8000 characters. Will it work !|||I'm afraid not. You can not concatenate text datatype
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
select col2 + col2 from test
(1 row(s) affected)
Server: Msg 403, Level 16, State 1, Line 2
Invalid operator for data type. Operator equals add, type equals text.|||Go thru this sample script,it may help u.
Use Northwind
go
select * into t from Employees
go
--inserting duplicate records----
insert into t
(
LastName,
FirstName,
Title,
TitleOfCourtesy,
BirthDate,
HireDate,
Address,
City,
Region,
PostalCode,
Country,
HomePhone,
Extension,
Photo,
Notes,
ReportsTo,
PhotoPath
)
select
LastName,
FirstName,
Title,
TitleOfCourtesy,
BirthDate,
HireDate,
Address,
City,
Region,
PostalCode,
Country,
HomePhone,
Extension,
Photo,
Notes,
ReportsTo,
PhotoPath
from t where EmployeeID in (2,1,4,5,8)
go
--------delete duplicate------
-- Use this query to findout the maximum lenth in text field.here maximum is 21000 bytes, so I used substring function up to that level(eg:substring(Photo,16001,8000)) in query.
--SELECT max(datalength(Photo)) FROM Employees
while (0=0)
begin
delete t from t t1 inner join
(
select max(E1ID) as E1ID,max(E2ID) as E2ID,sumvalue from
(
SELECT
E1.EmployeeID as E1ID
,E2.EmployeeID as E2ID, (E1.EmployeeID +E2.EmployeeID) as sumvalue
FROM
t E1 INNER JOIN t E2 ON
substring(E1.Photo, 1, 8000) = substring(E2.Photo, 1, 8000)
AND substring(E1.Photo, 8001, 8000) = substring(E2.Photo, 8001, 8000)
AND substring(E1.Photo, 16001, 8000) = substring(E2.Photo, 16001, 8000)
) as tm
group by sumvalue
having count(*)>=2
) tm1
on tm1.E1ID=t1.EmployeeID
if @.@.rowcount=0 break
end
go
--Unique records----
select * from t
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
MiraJTry converting the columns to, for instance, varchar by using CAST() and then concatenating them to one string. Before going on with DISTINCT or whatever needed.|||I want the output to be in the same datatype. Hence casting will not solve my purpose.|||nabucco's suggestion works:
select cast(col2 as text) col2
from (
select distinct cast(col2 AS varchar(100)) col2 from test
) x
col2
--
abc
dbc|||nabucco's suggestion works:
select cast(col2 as text) col2
from (
select distinct cast(col2 AS varchar(100)) col2 from test
) x
col2
--
abc
dbc
How will u solve the issue if the text data is bigger than 8000 ?|||Just a thought ...
Can use substring and then concatenate every 8000 characters. Will it work !|||I'm afraid not. You can not concatenate text datatype
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
select col2 + col2 from test
(1 row(s) affected)
Server: Msg 403, Level 16, State 1, Line 2
Invalid operator for data type. Operator equals add, type equals text.|||Go thru this sample script,it may help u.
Use Northwind
go
select * into t from Employees
go
--inserting duplicate records----
insert into t
(
LastName,
FirstName,
Title,
TitleOfCourtesy,
BirthDate,
HireDate,
Address,
City,
Region,
PostalCode,
Country,
HomePhone,
Extension,
Photo,
Notes,
ReportsTo,
PhotoPath
)
select
LastName,
FirstName,
Title,
TitleOfCourtesy,
BirthDate,
HireDate,
Address,
City,
Region,
PostalCode,
Country,
HomePhone,
Extension,
Photo,
Notes,
ReportsTo,
PhotoPath
from t where EmployeeID in (2,1,4,5,8)
go
--------delete duplicate------
-- Use this query to findout the maximum lenth in text field.here maximum is 21000 bytes, so I used substring function up to that level(eg:substring(Photo,16001,8000)) in query.
--SELECT max(datalength(Photo)) FROM Employees
while (0=0)
begin
delete t from t t1 inner join
(
select max(E1ID) as E1ID,max(E2ID) as E2ID,sumvalue from
(
SELECT
E1.EmployeeID as E1ID
,E2.EmployeeID as E2ID, (E1.EmployeeID +E2.EmployeeID) as sumvalue
FROM
t E1 INNER JOIN t E2 ON
substring(E1.Photo, 1, 8000) = substring(E2.Photo, 1, 8000)
AND substring(E1.Photo, 8001, 8000) = substring(E2.Photo, 8001, 8000)
AND substring(E1.Photo, 16001, 8000) = substring(E2.Photo, 16001, 8000)
) as tm
group by sumvalue
having count(*)>=2
) tm1
on tm1.E1ID=t1.EmployeeID
if @.@.rowcount=0 break
end
go
--Unique records----
select * from t
removing duplicate rows
I've been given a table that has hundreds of duplicate rows but I'm
having a bit of trouble trying to remove them, leaving just one unique
row, so maybe someone here can shed some light on it...
OK so here's the table spec:
VFEATURE
{
idVFeature unique identity
idFeature integer
idVehicle integer
}
There should only be ONE instance of each idFeature, idVehicle pair.
At the moment there are 100s.
I run the following query to identify which 'idFeature' values are
appearing more than once for each 'idVehicle'...
SELECT idFeature, idVehicle, COUNT(*) AS Expr1
FROM VFEATURE
GROUP BY idFeature, idVehicle
HAVING (COUNT(*) > 1)
Now that I have that... I've tried extracting all rows except one for
each duplicate, but with no success yet.
Anyone done something like this before?
Thanks,
Peter
--
"I hear ma train a comin'
... hear freedom comin"Check out: http://support.microsoft.com/?kbid=139444 for some techniques on
the same.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Stimp" <ren@.spumco.com> wrote in message
news:slrndngtd0.ipj.ren@.carbon.redbrick.dcu.ie...
> I've been given a table that has hundreds of duplicate rows but I'm
> having a bit of trouble trying to remove them, leaving just one unique
> row, so maybe someone here can shed some light on it...
> OK so here's the table spec:
> VFEATURE
> {
> idVFeature unique identity
> idFeature integer
> idVehicle integer
> }
> There should only be ONE instance of each idFeature, idVehicle pair.
> At the moment there are 100s.
> I run the following query to identify which 'idFeature' values are
> appearing more than once for each 'idVehicle'...
> SELECT idFeature, idVehicle, COUNT(*) AS Expr1
> FROM VFEATURE
> GROUP BY idFeature, idVehicle
> HAVING (COUNT(*) > 1)
> Now that I have that... I've tried extracting all rows except one for
> each duplicate, but with no success yet.
> Anyone done something like this before?
> Thanks,
> Peter
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>|||On Mon, 14 Nov 2005 Stimp <ren@.spumco.com> wrote:
> Anyone done something like this before?
I actually figured it out after posting.. don't you hate it when that
happens...
DELETE FROM VFeature
WHERE idVFeature IN
(SELECT MIN(idVFeature)
FROM VFeature
GROUP BY idFeature, idVehicle
HAVING COUNT(*) > 1)
"I hear ma train a comin'
... hear freedom comin"|||IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp (
idVFeature INT IDENTITY(1,1)
, idFeature INT NOT NULL
, idVehicle INT NOT NULL
, CONSTRAINT PK_#tmp PRIMARY KEY CLUSTERED ( idVFeature )
)
/* create sample data */
INSERT INTO
#tmp ( idFeature , idVehicle )
SELECT
idFeature
, idVehicle
FROM
( SELECT N AS idFeature FROM tblNumbers WHERE N BETWEEN 1 AND 10 ) Features
CROSS JOIN
( SELECT N AS idVehicle FROM tblNumbers WHERE N BETWEEN 1 AND 10 ) Vehicles
CROSS JOIN
( SELECT N AS N FROM tblNumbers WHERE N BETWEEN 1 AND 5 ) Duplicates
ORDER BY
idFeature
, n
, idVehicle
/* remove non-lowest idVFeature for unique idVehicle,idFeature combinations
*/
DELETE FROM
#tmp
FROM
#tmp
LEFT OUTER JOIN
( /* lowest idVFeature for unique idVehicle,idFeature combinations */
SELECT
MIN( idVFeature ) idVFeature
, idVehicle
, idFeature
FROM
#tmp
GROUP BY
idVehicle
, idFeature
) LowestVehicleFeatures
ON
#tmp.idVFeature = LowestVehicleFeatures.idVFeature
AND
#tmp.idVehicle = LowestVehicleFeatures.idVehicle
AND
#tmp.idFeature = LowestVehicleFeatures.idFeature
WHERE
LowestVehicleFeatures.idVFeature IS NULL
GO
/* add unique index to idVehicle , idFeature */
CREATE UNIQUE NONCLUSTERED INDEX UIX_#tmp_VehicleFeatures ON #tmp (
idVehicle , idFeature )
GO
SELECT * FROM #tmp WITH(INDEX(UIX_#tmp_VehicleFeatures)) ORDER BY idVehicle
, idFeature
"Stimp" <ren@.spumco.com> wrote in message
news:slrndngtd0.ipj.ren@.carbon.redbrick.dcu.ie...
> I've been given a table that has hundreds of duplicate rows but I'm
> having a bit of trouble trying to remove them, leaving just one unique
> row, so maybe someone here can shed some light on it...
> OK so here's the table spec:
> VFEATURE
> {
> idVFeature unique identity
> idFeature integer
> idVehicle integer
> }
> There should only be ONE instance of each idFeature, idVehicle pair.
> At the moment there are 100s.
> I run the following query to identify which 'idFeature' values are
> appearing more than once for each 'idVehicle'...
> SELECT idFeature, idVehicle, COUNT(*) AS Expr1
> FROM VFEATURE
> GROUP BY idFeature, idVehicle
> HAVING (COUNT(*) > 1)
> Now that I have that... I've tried extracting all rows except one for
> each duplicate, but with no success yet.
> Anyone done something like this before?
> Thanks,
> Peter
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>|||That would just delete the first instance of the duplicate.
Change "IN (...)" to "NOT IN ( ... )"
and you're sorted.
"Stimp" <ren@.spumco.com> wrote in message
news:slrndngvu1.b55.ren@.carbon.redbrick.dcu.ie...
> On Mon, 14 Nov 2005 Stimp <ren@.spumco.com> wrote:
> I actually figured it out after posting.. don't you hate it when that
> happens...
> DELETE FROM VFeature
> WHERE idVFeature IN
> (SELECT MIN(idVFeature)
> FROM VFeature
> GROUP BY idFeature, idVehicle
> HAVING COUNT(*) > 1)
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>|||On Mon, 14 Nov 2005 Rebecca York <rebecca.york> wrote:
> That would just delete the first instance of the duplicate.
> Change "IN (...)" to "NOT IN ( ... )"
> and you're sorted.
yeah I just ran it 10 times and it cleared out the table :)
Thanks,
Peter
> "Stimp" <ren@.spumco.com> wrote in message
> news:slrndngvu1.b55.ren@.carbon.redbrick.dcu.ie...
>
"I hear ma train a comin'
... hear freedom comin"|||Here is the process:
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Substitute your own names for those within the [].
Unique Column Names are those columns that uniquely identify each row.
Column Names is the full list of columns in your table.
HTH,
Mike
"Stimp" wrote:
> I've been given a table that has hundreds of duplicate rows but I'm
> having a bit of trouble trying to remove them, leaving just one unique
> row, so maybe someone here can shed some light on it...
> OK so here's the table spec:
> VFEATURE
> {
> idVFeature unique identity
> idFeature integer
> idVehicle integer
> }
> There should only be ONE instance of each idFeature, idVehicle pair.
> At the moment there are 100s.
> I run the following query to identify which 'idFeature' values are
> appearing more than once for each 'idVehicle'...
> SELECT idFeature, idVehicle, COUNT(*) AS Expr1
> FROM VFEATURE
> GROUP BY idFeature, idVehicle
> HAVING (COUNT(*) > 1)
> Now that I have that... I've tried extracting all rows except one for
> each duplicate, but with no success yet.
> Anyone done something like this before?
> Thanks,
> Peter
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>
having a bit of trouble trying to remove them, leaving just one unique
row, so maybe someone here can shed some light on it...
OK so here's the table spec:
VFEATURE
{
idVFeature unique identity
idFeature integer
idVehicle integer
}
There should only be ONE instance of each idFeature, idVehicle pair.
At the moment there are 100s.
I run the following query to identify which 'idFeature' values are
appearing more than once for each 'idVehicle'...
SELECT idFeature, idVehicle, COUNT(*) AS Expr1
FROM VFEATURE
GROUP BY idFeature, idVehicle
HAVING (COUNT(*) > 1)
Now that I have that... I've tried extracting all rows except one for
each duplicate, but with no success yet.
Anyone done something like this before?
Thanks,
Peter
--
"I hear ma train a comin'
... hear freedom comin"Check out: http://support.microsoft.com/?kbid=139444 for some techniques on
the same.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Stimp" <ren@.spumco.com> wrote in message
news:slrndngtd0.ipj.ren@.carbon.redbrick.dcu.ie...
> I've been given a table that has hundreds of duplicate rows but I'm
> having a bit of trouble trying to remove them, leaving just one unique
> row, so maybe someone here can shed some light on it...
> OK so here's the table spec:
> VFEATURE
> {
> idVFeature unique identity
> idFeature integer
> idVehicle integer
> }
> There should only be ONE instance of each idFeature, idVehicle pair.
> At the moment there are 100s.
> I run the following query to identify which 'idFeature' values are
> appearing more than once for each 'idVehicle'...
> SELECT idFeature, idVehicle, COUNT(*) AS Expr1
> FROM VFEATURE
> GROUP BY idFeature, idVehicle
> HAVING (COUNT(*) > 1)
> Now that I have that... I've tried extracting all rows except one for
> each duplicate, but with no success yet.
> Anyone done something like this before?
> Thanks,
> Peter
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>|||On Mon, 14 Nov 2005 Stimp <ren@.spumco.com> wrote:
> Anyone done something like this before?
I actually figured it out after posting.. don't you hate it when that
happens...
DELETE FROM VFeature
WHERE idVFeature IN
(SELECT MIN(idVFeature)
FROM VFeature
GROUP BY idFeature, idVehicle
HAVING COUNT(*) > 1)
"I hear ma train a comin'
... hear freedom comin"|||IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp (
idVFeature INT IDENTITY(1,1)
, idFeature INT NOT NULL
, idVehicle INT NOT NULL
, CONSTRAINT PK_#tmp PRIMARY KEY CLUSTERED ( idVFeature )
)
/* create sample data */
INSERT INTO
#tmp ( idFeature , idVehicle )
SELECT
idFeature
, idVehicle
FROM
( SELECT N AS idFeature FROM tblNumbers WHERE N BETWEEN 1 AND 10 ) Features
CROSS JOIN
( SELECT N AS idVehicle FROM tblNumbers WHERE N BETWEEN 1 AND 10 ) Vehicles
CROSS JOIN
( SELECT N AS N FROM tblNumbers WHERE N BETWEEN 1 AND 5 ) Duplicates
ORDER BY
idFeature
, n
, idVehicle
/* remove non-lowest idVFeature for unique idVehicle,idFeature combinations
*/
DELETE FROM
#tmp
FROM
#tmp
LEFT OUTER JOIN
( /* lowest idVFeature for unique idVehicle,idFeature combinations */
SELECT
MIN( idVFeature ) idVFeature
, idVehicle
, idFeature
FROM
#tmp
GROUP BY
idVehicle
, idFeature
) LowestVehicleFeatures
ON
#tmp.idVFeature = LowestVehicleFeatures.idVFeature
AND
#tmp.idVehicle = LowestVehicleFeatures.idVehicle
AND
#tmp.idFeature = LowestVehicleFeatures.idFeature
WHERE
LowestVehicleFeatures.idVFeature IS NULL
GO
/* add unique index to idVehicle , idFeature */
CREATE UNIQUE NONCLUSTERED INDEX UIX_#tmp_VehicleFeatures ON #tmp (
idVehicle , idFeature )
GO
SELECT * FROM #tmp WITH(INDEX(UIX_#tmp_VehicleFeatures)) ORDER BY idVehicle
, idFeature
"Stimp" <ren@.spumco.com> wrote in message
news:slrndngtd0.ipj.ren@.carbon.redbrick.dcu.ie...
> I've been given a table that has hundreds of duplicate rows but I'm
> having a bit of trouble trying to remove them, leaving just one unique
> row, so maybe someone here can shed some light on it...
> OK so here's the table spec:
> VFEATURE
> {
> idVFeature unique identity
> idFeature integer
> idVehicle integer
> }
> There should only be ONE instance of each idFeature, idVehicle pair.
> At the moment there are 100s.
> I run the following query to identify which 'idFeature' values are
> appearing more than once for each 'idVehicle'...
> SELECT idFeature, idVehicle, COUNT(*) AS Expr1
> FROM VFEATURE
> GROUP BY idFeature, idVehicle
> HAVING (COUNT(*) > 1)
> Now that I have that... I've tried extracting all rows except one for
> each duplicate, but with no success yet.
> Anyone done something like this before?
> Thanks,
> Peter
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>|||That would just delete the first instance of the duplicate.
Change "IN (...)" to "NOT IN ( ... )"
and you're sorted.
"Stimp" <ren@.spumco.com> wrote in message
news:slrndngvu1.b55.ren@.carbon.redbrick.dcu.ie...
> On Mon, 14 Nov 2005 Stimp <ren@.spumco.com> wrote:
> I actually figured it out after posting.. don't you hate it when that
> happens...
> DELETE FROM VFeature
> WHERE idVFeature IN
> (SELECT MIN(idVFeature)
> FROM VFeature
> GROUP BY idFeature, idVehicle
> HAVING COUNT(*) > 1)
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>|||On Mon, 14 Nov 2005 Rebecca York <rebecca.york> wrote:
> That would just delete the first instance of the duplicate.
> Change "IN (...)" to "NOT IN ( ... )"
> and you're sorted.
yeah I just ran it 10 times and it cleared out the table :)
Thanks,
Peter
> "Stimp" <ren@.spumco.com> wrote in message
> news:slrndngvu1.b55.ren@.carbon.redbrick.dcu.ie...
>
"I hear ma train a comin'
... hear freedom comin"|||Here is the process:
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Substitute your own names for those within the [].
Unique Column Names are those columns that uniquely identify each row.
Column Names is the full list of columns in your table.
HTH,
Mike
"Stimp" wrote:
> I've been given a table that has hundreds of duplicate rows but I'm
> having a bit of trouble trying to remove them, leaving just one unique
> row, so maybe someone here can shed some light on it...
> OK so here's the table spec:
> VFEATURE
> {
> idVFeature unique identity
> idFeature integer
> idVehicle integer
> }
> There should only be ONE instance of each idFeature, idVehicle pair.
> At the moment there are 100s.
> I run the following query to identify which 'idFeature' values are
> appearing more than once for each 'idVehicle'...
> SELECT idFeature, idVehicle, COUNT(*) AS Expr1
> FROM VFEATURE
> GROUP BY idFeature, idVehicle
> HAVING (COUNT(*) > 1)
> Now that I have that... I've tried extracting all rows except one for
> each duplicate, but with no success yet.
> Anyone done something like this before?
> Thanks,
> Peter
> --
> "I hear ma train a comin'
> ... hear freedom comin"
>
Saturday, February 25, 2012
Removing duplicate Records
I have a table that holds the following
1 7530568 87143 OESCHD 1/5/2006 6:31:58 AM
1 7530568 87143 OESCHD 1/5/2006 7:02:36 AM
for each 7530568 ordernumber there should only be one OESCHD status.
This is the query I'm using to insert the data sent to me.
INSERT INTO ORDER_EVENTS
SELECT d.division as division,
dt.orderNum as orderNum,
dt.poNum as poNum,
dt.statusCode as statusCode,
dt.statusChangeDate as statusChangeDate
FROM dt_Order_Events dt INNER JOIN
division d ON dt.division = d.divisionShort INNER JOIN
status s ON s.division = d.division AND s.statCode = dt.statusCode
WHERE directive <> 'C' AND
dt.orderNum IN (SELECT orderNum FROM ORDER_HEADER)
This works fine when used with in the hourly transactional update. But When I ran it for the Bulk UpDate (so we'd have historical data) it allowed orders to have statuses to many times.
I am not a SQL guru, I have no idea how to write a sql statement or stored proc that will remove the duplicate records. or how to change what I have to prevent further ones.
Any help would be apreciated.
look here:
http://www.sql-server-performance.com/dv_delete_duplicates.asp
you will get your answers there.
tomer
removing duplicate entries in SQL Field
Hi All,
Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.
Below is the SQL and the results from the returned page.
PS I tried to use distinct but with no luck I want just one of each in the example below.
Thanks in Advance!
Scott
==============================
SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "
SQL1 = ""
SQL1 = SQL
SQL = SQL & "WHERE " & Where & " "
==================================
Here is a piece of the problem field, note repeating colors etc.
CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11
Hi,
if you are using denormalized stored values, you will first have denormalized them to use SQL Server functions for grouping / distinct them. You will have to separate the values first using a separator and using a function like this one here:
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Removing Duplicate Entries from a Parent-Child Hierarchy
I have defined a Parent-Child hierarchy for one of my dimensions. (It is a flavour of organisation structure)
However, when I process it, each node repeats itself as one of its own children.
What is going on here and how can I fix this? Someone mentioned a property I can set, but nothing jumps out at me.
It's the datamember, no? If you drag and drop theses members for a MDX query it brings the .datamember in the end of the member? If so, just set to hidden the datamember.
The parent-child attribute has a property named "MemberWithData" set to "NonLeafDataHidden".
|||Handerson is right.
You have the change the default property to get the desired effect.
The default scenario which AS2005 is where a sales mgr and his salesmen all sell products and you wnat to display revenue for them.
The alternative might be that only the salesmen sell and the manager does not have any sales, Then you ahve to reconfigure the property defined above.
|||I don't understand what you guys are talking about... when I browse the hierarchy of my parent child relationship, for each parent node there is a child node with the same name and properties when the dimension is processed. This is before we even run any MDX...
|||Let's look a sample, if you have the parent-child dimension entity like that:
America|||Thanks for the clarification ... much appreciated.
Removing duplicate entries
Hi,
I would lke to know what is the best way to remove duplicate entries from a GIANT table ?
I'm using this but I think that too slow..
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[#PABX_TEMP]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[#PABX_TEMP]
CREATE TABLE [#PABX_TEMP] (
[CHAVE] [int],
[COD_CLIENTE] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DATA_HORA] [datetime] NULL ,
[NRTELEFONE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[RAMAL] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[WCOS] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TEMPO_SEGUNDOS] [real] NULL ,
[TIPO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TIPO_ORIGINAL] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TRONCO] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[TEMPO_ATENDIMENTO] [real] NULL ,
[JAPROCESSADO] [int] NULL ,
[VALOR] [money] NULL ,
[VALOR_CONC] [money] NULL ,
[VALOR_TARIFA] [money] NULL ,
[VALOR_TARIFA_CONC] [money] NULL ,
[CLASSIFICA] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[LOCALIDADE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[VALOR_TEMPO] [money] NULL ,
[NUMERO_E1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[BLOQUEADO] [bit] NULL ,
[DATA_BLOQUEIO] [datetime] NULL ,
[TRANSFERIDO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO #PABX_TEMP
SELECT MIN(CHAVE) as 'CHAVE', [COD_CLIENTE], [DATA_HORA], [NRTELEFONE],
[RAMAL], [WCOS], [TEMPO_SEGUNDOS], [TIPO], [TIPO_ORIGINAL],
[TRONCO], [TEMPO_ATENDIMENTO], [JAPROCESSADO], [VALOR], [VALOR_CONC],
[VALOR_TARIFA], [VALOR_TARIFA_CONC], [CLASSIFICA], [LOCALIDADE],
[VALOR_TEMPO], [NUMERO_E1], [BLOQUEADO], [DATA_BLOQUEIO], [TRANSFERIDO]
FROM PABX WHERE (BLOQUEADO = 0 OR BLOQUEADO IS NULL)
GROUP BY [COD_CLIENTE], [DATA_HORA], [NRTELEFONE],
[RAMAL], [WCOS], [TEMPO_SEGUNDOS], [TIPO], [TIPO_ORIGINAL],
[TRONCO], [TEMPO_ATENDIMENTO], [JAPROCESSADO], [VALOR], [VALOR_CONC],
[VALOR_TARIFA], [VALOR_TARIFA_CONC], [CLASSIFICA], [LOCALIDADE],
[VALOR_TEMPO], [NUMERO_E1], [BLOQUEADO], [DATA_BLOQUEIO], [TRANSFERIDO]
DELETE FROM PABX WHERE (BLOQUEADO = 0 OR BLOQUEADO IS NULL) AND CHAVE NOT IN (SELECT CHAVE FROM #PABX_TEMP)
DROP TABLE #PABX_TEMP
Thanks
Try replacing your NOT IN with NOT EXIST; NOT EXIST tends to be more efficient. Here are some posts to reference:
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532892&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1
OK, I'm gonna test
Thanks
Removing duplicate data
I have a table that I need to remove duplicates from and the table includes
an identity column. The table contains 12 fields but our business rules are
that only 3 of the fields can make the record a duplicate. We would like to
keep the record with the min identity column. I have done this in the past,
but it was about 5 years ago and I did not insert the dups into another
table, I did it strictly with a query. Can anybody please help me out with
some code to take care of this or any suggestions on a better way of doing
this? So something along the lines of grouping the data by the fields that
we are interested in and then deleting the records where the identity column
is greater than the min identity column.
Thanks.This is the duplicates along with the
minimum id per group
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1
Join this to the original table
to give the ids of the duplicates
excluding the minimum id.
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
Put it all together to delete them
DELETE
FROM sometable
WHERE id IN (
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
)|||Do you need to export the removed duplicates to another table?
Try something like this:
INSERT INTO RemDup (...) SELECT ... FROM MyTab AS t1 WHERE EXISTS (SELECT
NULL FROM MyTab AS t2 WHERE t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND
t1.Col2=t2.Col2 AND t1.ID>t2.ID)
DELETE FROM MyTab AS t1 WHERE EXISTS (SELECT NULL FROM MyTab AS t2 WHERE
t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND t1.Col2=t2.Col2 AND t1.ID>t2.ID)
HTH,
Axel Dahmen
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
> I have a table that I need to remove duplicates from and the table
includes
> an identity column. The table contains 12 fields but our business rules
are
> that only 3 of the fields can make the record a duplicate. We would like
to
> keep the record with the min identity column. I have done this in the
past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
that
> we are interested in and then deleting the records where the identity
column
> is greater than the min identity column.
> Thanks.|||Here's an example. This will delete all but 1 of the dupes. You will need to
change table / col name accordingly
DELETE FROM _Holding_table
WHERE EXISTS(SELECT NULL FROM _Holding_table s1
WHERE s1.PhoneNumber= _Holding_table.PhoneNumber
and s1.PK_Holding_TableID > _Holding_table.PK_Holding_TableID)
HTH. Ryan
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
>I have a table that I need to remove duplicates from and the table includes
> an identity column. The table contains 12 fields but our business rules
> are
> that only 3 of the fields can make the record a duplicate. We would like
> to
> keep the record with the min identity column. I have done this in the
> past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
> with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
> that
> we are interested in and then deleting the records where the identity
> column
> is greater than the min identity column.
> Thanks.|||This is untested, perhaps you wrap this in a transaction first:
DELETE FROM SomeTable S
WHERE Idcolumn NOT IN
(
SELECT MIN(idcolumn)
FROM SOMETABLE S2
GROUP BY FirstColumn1,FirstColumn2,FirstColumn3
)
HTH, Jens Suessmeyer.
an identity column. The table contains 12 fields but our business rules are
that only 3 of the fields can make the record a duplicate. We would like to
keep the record with the min identity column. I have done this in the past,
but it was about 5 years ago and I did not insert the dups into another
table, I did it strictly with a query. Can anybody please help me out with
some code to take care of this or any suggestions on a better way of doing
this? So something along the lines of grouping the data by the fields that
we are interested in and then deleting the records where the identity column
is greater than the min identity column.
Thanks.This is the duplicates along with the
minimum id per group
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1
Join this to the original table
to give the ids of the duplicates
excluding the minimum id.
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
Put it all together to delete them
DELETE
FROM sometable
WHERE id IN (
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
)|||Do you need to export the removed duplicates to another table?
Try something like this:
INSERT INTO RemDup (...) SELECT ... FROM MyTab AS t1 WHERE EXISTS (SELECT
NULL FROM MyTab AS t2 WHERE t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND
t1.Col2=t2.Col2 AND t1.ID>t2.ID)
DELETE FROM MyTab AS t1 WHERE EXISTS (SELECT NULL FROM MyTab AS t2 WHERE
t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND t1.Col2=t2.Col2 AND t1.ID>t2.ID)
HTH,
Axel Dahmen
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
> I have a table that I need to remove duplicates from and the table
includes
> an identity column. The table contains 12 fields but our business rules
are
> that only 3 of the fields can make the record a duplicate. We would like
to
> keep the record with the min identity column. I have done this in the
past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
that
> we are interested in and then deleting the records where the identity
column
> is greater than the min identity column.
> Thanks.|||Here's an example. This will delete all but 1 of the dupes. You will need to
change table / col name accordingly
DELETE FROM _Holding_table
WHERE EXISTS(SELECT NULL FROM _Holding_table s1
WHERE s1.PhoneNumber= _Holding_table.PhoneNumber
and s1.PK_Holding_TableID > _Holding_table.PK_Holding_TableID)
HTH. Ryan
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
>I have a table that I need to remove duplicates from and the table includes
> an identity column. The table contains 12 fields but our business rules
> are
> that only 3 of the fields can make the record a duplicate. We would like
> to
> keep the record with the min identity column. I have done this in the
> past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
> with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
> that
> we are interested in and then deleting the records where the identity
> column
> is greater than the min identity column.
> Thanks.|||This is untested, perhaps you wrap this in a transaction first:
DELETE FROM SomeTable S
WHERE Idcolumn NOT IN
(
SELECT MIN(idcolumn)
FROM SOMETABLE S2
GROUP BY FirstColumn1,FirstColumn2,FirstColumn3
)
HTH, Jens Suessmeyer.
Removing duplicate dat
Hi
I have a 1.5 million row table with a text field. Can
anybody think of a way to remove any duplicate rows where the text field
contains the same data (and a
way which will not take days to run!)
For example if I have two rows with "SQL SERVER IS GREAT", I want to remove
one of them.
Thanks
here is an example of something that works for me:
create table textstuff
(pk int not null primary key,
textcol text)
go
--insert statements
declare @.int int
select @.int=max(datalength(textcol)) from textstuff
select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
textstuff order by 2
select pk, holding.checksum from holding,
(select checksum, test=count(checksum) from holding group by checksum having
count(checksum) >1) as a
where holding.checksum=a.checksum
rows which have identical checksums will show up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul" <xx@.nospam.com> wrote in message
news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a 1.5 million row table with a text field. Can
> anybody think of a way to remove any duplicate rows where the text field
> contains the same data (and a
> way which will not take days to run!)
> For example if I have two rows with "SQL SERVER IS GREAT", I want to
remove
> one of them.
> Thanks
>
>
|||That's awsome, thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u4beTGwuEHA.3808@.TK2MSFTNGP15.phx.gbl...
> here is an example of something that works for me:
> create table textstuff
> (pk int not null primary key,
> textcol text)
> go
> --insert statements
> declare @.int int
> select @.int=max(datalength(textcol)) from textstuff
> select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
> textstuff order by 2
> select pk, holding.checksum from holding,
> (select checksum, test=count(checksum) from holding group by checksum
having
> count(checksum) >1) as a
> where holding.checksum=a.checksum
>
> rows which have identical checksums will show up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Paul" <xx@.nospam.com> wrote in message
> news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> remove
>
I have a 1.5 million row table with a text field. Can
anybody think of a way to remove any duplicate rows where the text field
contains the same data (and a
way which will not take days to run!)
For example if I have two rows with "SQL SERVER IS GREAT", I want to remove
one of them.
Thanks
here is an example of something that works for me:
create table textstuff
(pk int not null primary key,
textcol text)
go
--insert statements
declare @.int int
select @.int=max(datalength(textcol)) from textstuff
select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
textstuff order by 2
select pk, holding.checksum from holding,
(select checksum, test=count(checksum) from holding group by checksum having
count(checksum) >1) as a
where holding.checksum=a.checksum
rows which have identical checksums will show up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul" <xx@.nospam.com> wrote in message
news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a 1.5 million row table with a text field. Can
> anybody think of a way to remove any duplicate rows where the text field
> contains the same data (and a
> way which will not take days to run!)
> For example if I have two rows with "SQL SERVER IS GREAT", I want to
remove
> one of them.
> Thanks
>
>
|||That's awsome, thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u4beTGwuEHA.3808@.TK2MSFTNGP15.phx.gbl...
> here is an example of something that works for me:
> create table textstuff
> (pk int not null primary key,
> textcol text)
> go
> --insert statements
> declare @.int int
> select @.int=max(datalength(textcol)) from textstuff
> select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
> textstuff order by 2
> select pk, holding.checksum from holding,
> (select checksum, test=count(checksum) from holding group by checksum
having
> count(checksum) >1) as a
> where holding.checksum=a.checksum
>
> rows which have identical checksums will show up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Paul" <xx@.nospam.com> wrote in message
> news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> remove
>
Subscribe to:
Posts (Atom)