another question

Thanks for the note Vishal,
What if each had a date. So that
create table #cartype(manufacturer varchar(500), itemnumber int, datemade date)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
So that
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
Would get deleted because there the exact same records (same number) of records are duplicated for that date.
But the records:
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
The GE records would stay because all of the records are not duplicated, just the GE records are so I want to keep all the records.
Thanks for any ideas!
Try query as follows:
delete a
from #cartype a join
(select manufacturer, datemade, count(*) cnt
from #cartype
group by manufacturer, datemade
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.datemade = b.datemade and b.cnt <>
(select count(*)
from #cartype x
group by manufacturer
having x.manufacturer = b.manufacturer)
Vishal Parkar
vgparkar@.yahoo.co.in
|||Here is what I have done to make it fit my query. Here are my records:
store, deliverydate, itemnumber, qty
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
(this should get deleted, exact same as 2 lines above)
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,090495,020
007SS,04/15/2004,090495,020
(all lines should stay because it is not exact same.)
selext a.*, cnt
from tblItemOrder a join
(select itemnumber, quantity, store, deliverydate, count(*) cnt
from tblItemOrder
group by itemnumber, quantity, store, deliverydate
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.quantity = b.quantity and a.store = b.store and a.deliverydate = b.deliverydate and b.cnt <>
(select count(*)
from tblItemOrder x
group by store, deliverydate
having x.store = b.store and x.deliverydate = b.deliverydate)
I get all records that have duplicate lines, not just the ones with same count of duplicate records (storeno, deliverydate).
Any ideas? I have thought about it many different ways and have not come up with a solution yet. Thanks again,
|||hi ashley,
Remember SELECT and DELETE are different statements. DELETE will delete
the data from the table while with the help of SELECT statement you can
filterout the rows from the table.
See following example:
create table tt
(store varchar(50),
deliverydate datetime,
itemnumber varchar(50),
qty int)
--insert some data
insert into tt
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','090495','020' union all
select '007SS','04/15/2004','090495','020'
--Try this query:
select a.*
from tt a join
(select store, deliverydate, itemnumber,count(*) cnt
from tt
group by store, deliverydate, itemnumber
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.store = b.store and a.deliverydate = b.deliverydate and 1 not in
(select 1
from tt x
group by store, deliverydate, itemnumber
having x.store = b.store and x.deliverydate = b.deliverydate and
x.itemnumber <> b.itemnumber and count(*) = b.cnt)
Vishal Parkar
vgparkar@.yahoo.co.in