Duplicate Entries Help

Hi there,
I have a table called userchartentry and it has these fields:
ID, DMCUSERID, CHARTPERIODID, TRACKID, POSITION
This table is filled with users charts, and each chart has 20 tracks, hence
the position field. The problem is our Chart Application has gone crazy and
duplicated each track up to 5 times in each chart. Sometimes a chart will
have 5 position 1 tracks, but 4 position 2 tracks.
I would like to write some SQL to delete the duplicate entries. I cant think
how to do this, if anyone can think of a solution then a cyber beer is
yours, plus lots and lots of thanks.
Cheers,
Steve
If ID is an identity column (which I assume it is):
DELETE FROM userchartentry
WHERE EXISTS(SELECT NULL FROM userchartentry u1
WHERE u1.ID < userchartentry .ID
AND u1.DMCUSERID = userchartentry .DMCUSERID
AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
AND u1.TRACKID = userchartentry .TRACKID
AND u1.POSITION = userchartentry .POSITION)
then create a primary key or unique constraint on the columns that make up
the natural key to prevent it from happening again.
Jacco Schalkwijk
SQL Server MVP
"Dooza" <steve@.whatareyoudooza.tv> wrote in message
news:u$ZirFgqEHA.708@.tk2msftngp13.phx.gbl...
> Hi there,
> I have a table called userchartentry and it has these fields:
> ID, DMCUSERID, CHARTPERIODID, TRACKID, POSITION
> This table is filled with users charts, and each chart has 20 tracks,
> hence
> the position field. The problem is our Chart Application has gone crazy
> and
> duplicated each track up to 5 times in each chart. Sometimes a chart will
> have 5 position 1 tracks, but 4 position 2 tracks.
> I would like to write some SQL to delete the duplicate entries. I cant
> think
> how to do this, if anyone can think of a solution then a cyber beer is
> yours, plus lots and lots of thanks.
> Cheers,
> Steve
>
|||Hi Jacco,
That was just about perfect. I created a test table with the same data and
ran the command, when I looked at the results I found 18 DMCUSERIDs with
more than 20 tracks. This I can handle very happily, they are easy to
delete.
Thank you very much for helping!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:erpIzdgqEHA.1164@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> If ID is an identity column (which I assume it is):
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> then create a primary key or unique constraint on the columns that make up
> the natural key to prevent it from happening again.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:u$ZirFgqEHA.708@.tk2msftngp13.phx.gbl...
will
>
|||Hi Jacco,
Before I do this to my live database, if I want to filter by CHARTPERIODID =
8414 where would that go in the statement?
Cheers!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:erpIzdgqEHA.1164@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> If ID is an identity column (which I assume it is):
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> then create a primary key or unique constraint on the columns that make up
> the natural key to prevent it from happening again.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:u$ZirFgqEHA.708@.tk2msftngp13.phx.gbl...
will
>
|||Just add it at the end:
DELETE FROM userchartentry
WHERE EXISTS(SELECT NULL FROM userchartentry u1
WHERE u1.ID < userchartentry .ID
AND u1.DMCUSERID = userchartentry .DMCUSERID
AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
AND u1.TRACKID = userchartentry .TRACKID
AND u1.POSITION = userchartentry .POSITION)
AND CHARTPERIODID = 8414
Jacco Schalkwijk
SQL Server MVP
"Dooza" <steve@.whatareyoudooza.tv> wrote in message
news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
> Hi Jacco,
> Before I do this to my live database, if I want to filter by CHARTPERIODID
> =
> 8414 where would that go in the statement?
> Cheers!
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote
> in message news:erpIzdgqEHA.1164@.TK2MSFTNGP10.phx.gbl...
> will
>
|||Hi Jacco,
I thought as much, thanks again! I just removed 20,000 rows of duplicate
data.
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Just add it at the end:
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> AND CHARTPERIODID = 8414
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
CHARTPERIODID[vbcol=seagreen]
crazy[vbcol=seagreen]
cant[vbcol=seagreen]
is
>
|||Hi Jacco,
I was wondering if you could help me some more?
I want to display the following data from this same table:
CHARTPERIODID and NUMBER OF UNIQUE CHARTS
This is what I first tried:
SELECT CHARTPERIODID, COUNT(DMCUSERID) AS CHARTS
FROM dbo.UserChartEntry
GROUP BY CHARTPERIODID
But due to there being more than 1 row per DMCUSERID and CHARTPERIODID the
count for CHARTS is wrong. So I then tried this:
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Just add it at the end:
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> AND CHARTPERIODID = 8414
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
CHARTPERIODID[vbcol=seagreen]
crazy[vbcol=seagreen]
cant[vbcol=seagreen]
is
>
|||Hi Jacco,
I was wondering if you could give me some further help relating to this same
table.
I want to display the total number of charts submitted in each Chart Period.
Since there are up to 20 rows per DMCUSERID and per CHARTPERIODID I thought
using DISTINCT would help. This is what I have tried:
SELECT CHARTPERIODID, COUNT(DISTINCT DMCUSERID) AS CHARTS
FROM dbo.UserChartEntry
GROUP BY CHARTPERIODID
Unfortunatly I get a Timeout when running this. I am doing this correctly?
Is there another way to do what I want that wont Timeout?
Any help would be great.
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Just add it at the end:
> DELETE FROM userchartentry
> WHERE EXISTS(SELECT NULL FROM userchartentry u1
> WHERE u1.ID < userchartentry .ID
> AND u1.DMCUSERID = userchartentry .DMCUSERID
> AND u1.CHARTPERIODID = userchartentry .CHARTPERIODID
> AND u1.TRACKID = userchartentry .TRACKID
> AND u1.POSITION = userchartentry .POSITION)
> AND CHARTPERIODID = 8414
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:ehaP51gqEHA.1992@.TK2MSFTNGP09.phx.gbl...
CHARTPERIODID[vbcol=seagreen]
crazy[vbcol=seagreen]
cant[vbcol=seagreen]
is
>
|||Dooza
You will be better of to use an indexed view for such kind of report
Look at below example written Steve Kass.
reate table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"Dooza" <steve@.whatareyoudooza.tv> wrote in message
news:%23PXLHMtqEHA.2724@.TK2MSFTNGP14.phx.gbl...
> Hi Jacco,
> I was wondering if you could give me some further help relating to this
same
> table.
> I want to display the total number of charts submitted in each Chart
Period.
> Since there are up to 20 rows per DMCUSERID and per CHARTPERIODID I
thought
> using DISTINCT would help. This is what I have tried:
> SELECT CHARTPERIODID, COUNT(DISTINCT DMCUSERID) AS CHARTS
> FROM dbo.UserChartEntry
> GROUP BY CHARTPERIODID
> Unfortunatly I get a Timeout when running this. I am doing this correctly?
> Is there another way to do what I want that wont Timeout?
> Any help would be great.
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
> CHARTPERIODID
make[vbcol=seagreen]
tracks,[vbcol=seagreen]
> crazy
chart
> cant
> is
>
|||Hi Uri,
Thank you for the help, but that really is beyond my abilities. I was
thinking more along the lines of using EXISTS but havent really figured it
all out yet.
Thanks anyway!
Dooza
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHbtiVtqEHA.3464@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Dooza
> You will be better of to use an indexed view for such kind of report
> Look at below example written Steve Kass.
> reate table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:%23PXLHMtqEHA.2724@.TK2MSFTNGP14.phx.gbl...
> same
> Period.
> thought
correctly?[vbcol=seagreen]
> wrote
<jacco.please.reply@.to.newsgroups.mvps.org.invalid >[vbcol=seagreen]
> make
> tracks,
> chart
beer
>