Deleting semi duplicates

Suppose that I have a table that contains a lot of records that are
identical except for an id field and a date-time-stamp field. For
example

Id Unit Price DTS
1 A 1.00 Date 1
2 A 1.00 Date 2
3 A 1.00 Date 3
4 B 1.25 Date 4
5 B 1.50 Date 5
6 B 1.50 Date 6
7 C 2.75 Date 7
8 C 2.75 Date 8
9 C 2.75 Date 9
10 C 3.00 Date 10

I want to cull out records that are duplicates in the units and price
fields. I want to use the max DTS as the criteria for which record in
a set of "duplicates" will remain. So, If I get the right query, I
should return with

Id Unit Price DTS
1 A 1.00 Date 1
4 B 1.25 Date 4
5 B 1.50 Date 5
7 C 2.75 Date 7
10 C 3.00 Date 10

Is this possible using a single query? If so, how? I am sure that I
can do this using code, but it will involve a bunch of loops and
process time. I would prefer a cleaner, more elegant way. Thanks for
any help.

JerryAssuming the combination of (unit,price,dts) is unique and non-NULL:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE unit = Sometable.unit
AND price = Sometable.price
AND dts > Sometable.dts)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<zemdnRnYsNSQopHdRVn-vw@.giganews.com>...
> Assuming the combination of (unit,price,dts) is unique and non-NULL:
> DELETE FROM Sometable
> WHERE EXISTS
> (SELECT *
> FROM Sometable AS S
> WHERE unit = Sometable.unit
> AND price = Sometable.price
> AND dts > Sometable.dts)

Thanks. I'll give this a try.

J|||Remember that names are resoved to the nearer containing table
reference! You meant:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE S.unit = Sometable.unit
AND S.price = Sometable.price
AND S.dts > Sometable.dts);|||> Remember that names are resoved to the nearer containing table
> reference!

Precisely. That's why the S isn't needed here - the alias ensures that
"Sometable" refers to the outer reference and the other columns to the inner
reference. Your statement is equivalent to mine.

--
David Portas
----
Please reply only to the newsgroup
--sql

deleting duplicate records

dear friends,
suppose i've one table, it has only to rows.the two rows are are same as it is.how can i delete one row from that table?

thank you verymuchSELECT DISTINCT *
INTO #HOLDING
FROM >your table<
GO
TRUNCATE TABLE >your table<
GO
INSERT >your table<
SELECT *
FROM #HOLDING
GO
DROP TABLE #HOLDING
GO|||you can add another column of type bigint make it auto increment by setting Identity properties to Yes. then you can mannually delete the row :)

Quote:

Originally Posted by vinod

dear friends,
suppose i've one table, it has only to rows.the two rows are are same as it is.how can i delete one row from that table?

thank you verymuch

sql

Deleted record

Suppose I delete a record in a database at 12pm.
And one staff modified the same record in another database at 1 pm.
Aftere the merge replication, does that record still in both databases or
not.
By default a row deleted at the publisher will be replicated to all
subscribers as a delete. The updated row on the subscriber would be logged
as a delete. A delete originating at all subscribers would be logged as a
conflict and be replaced with an update flowing from the subscriber.
If a row was deleted at a subscriber and then updated at another subscriber
the first one in would remain, and the later one would be rolled back and
logged in the conflict table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
> Suppose I delete a record in a database at 12pm.
> And one staff modified the same record in another database at 1 pm.
> Aftere the merge replication, does that record still in both databases or
> not.
>
|||Not really.
If you had the following:
UserA deletes row at noon on Server1 and UserB updates that row on Server2
at 1PM, if the merge engine does not synchronize between the delete and the
update, then you have created a conflict that I refer to "update of a
non-existent row". In this case, if your conflict resolution states that
Server1 should win, then the delete will go through to both servers and the
update will be logged in the conflict table at the publisher. If your
conflict resolution states that Server2 should win, then the update is
applied to both servers and the delete is logged to the conflict table at
the publisher.
However, if UserA deletes row at noon on Server1 and UserB updates that row
on Server2 at 1PM, and the merge engine performed a synchronization cycle
between the delete and the update, then nothing would happen. The update
would change zero rows since the row would not exist in the database. Since
no rows had changed, the merge update trigger would simply return without
logging anything into MSmerge_contents.
The piece of the merge trigger that governs this behavior is as follows:
declare @.article_rows_updated int
select @.article_rows_updated = count(*) from inserted
if @.article_rows_updated=0
return
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eCpAC8sKGHA.2064@.TK2MSFTNGP11.phx.gbl...
> By default a row deleted at the publisher will be replicated to all
> subscribers as a delete. The updated row on the subscriber would be logged
> as a delete. A delete originating at all subscribers would be logged as a
> conflict and be replaced with an update flowing from the subscriber.
> If a row was deleted at a subscriber and then updated at another
> subscriber the first one in would remain, and the later one would be
> rolled back and logged in the conflict table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23oN$CLqKGHA.140@.TK2MSFTNGP12.phx.gbl...
>