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,
SteveIf 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...
> 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,
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...
> 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
> >
> >
>|||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...
>> 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,
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...
> 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...
> >> 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,
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...
> 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...
> >> 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,
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...
> 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...
> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>|||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
> in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
> > 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...
> > >> 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 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...
> 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
> > in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
> > > 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...
> > > >> 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
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> >
> >
>|||The SQL is the correct way to do it. If you have a time out, it will
probably help to create an index on the table:
CREATE NONCLUSTERED INDEX ix_UserChartEntry ON UserChartEntry(CHARTPERIODID,
DMCUSERID)
Creating the query itself might take quite some time as well though.
--
Jacco Schalkwijk
SQL Server MVP
"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
> in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
>> 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...
>> >> 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,
So once I have created the index do I just run my previous select statement
as normal?
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:Oy9P8ztqEHA.708@.tk2msftngp13.phx.gbl...
> The SQL is the correct way to do it. If you have a time out, it will
> probably help to create an index on the table:
> CREATE NONCLUSTERED INDEX ix_UserChartEntry ON
UserChartEntry(CHARTPERIODID,
> DMCUSERID)
> Creating the query itself might take quite some time as well though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "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
> > in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
> >> 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...
> >> >> 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Yep.
--
Jacco Schalkwijk
SQL Server MVP
"Dooza" <steve@.whatareyoudooza.tv> wrote in message
news:uawXX7tqEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi Jacco,
> So once I have created the index do I just run my previous select
> statement
> as normal?
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:Oy9P8ztqEHA.708@.tk2msftngp13.phx.gbl...
>> The SQL is the correct way to do it. If you have a time out, it will
>> probably help to create an index on the table:
>> CREATE NONCLUSTERED INDEX ix_UserChartEntry ON
> UserChartEntry(CHARTPERIODID,
>> DMCUSERID)
>> Creating the query itself might take quite some time as well though.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "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
>> > in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
>> >> 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...
>> >> >> 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,
I ran the index and the view ran the quickest I have ever seen! I am not
sure if the results are what I was expecting, but at least it ran :)
Thanks once again for the help!
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23ScmBJwqEHA.192@.tk2msftngp13.phx.gbl...
> Yep.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Dooza" <steve@.whatareyoudooza.tv> wrote in message
> news:uawXX7tqEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > Hi Jacco,
> > So once I have created the index do I just run my previous select
> > statement
> > as normal?
> >
> > Steve
> >
> > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> > wrote
> > in message news:Oy9P8ztqEHA.708@.tk2msftngp13.phx.gbl...
> >> The SQL is the correct way to do it. If you have a time out, it will
> >> probably help to create an index on the table:
> >>
> >> CREATE NONCLUSTERED INDEX ix_UserChartEntry ON
> > UserChartEntry(CHARTPERIODID,
> >> DMCUSERID)
> >>
> >> Creating the query itself might take quite some time as well though.
> >>
> >> --
> >> Jacco Schalkwijk
> >> SQL Server MVP
> >>
> >>
> >> "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
> >> > in message news:%23Y7ZVChqEHA.1712@.tk2msftngp13.phx.gbl...
> >> >> 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...
> >> >> >> 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
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>