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

Delete partial from column

Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".

i.e.:

rrb7@.yahoo.com;richard.butcher@.sthou.com;administr ator@.sthou.com

etc like that.
each row varies with one exception. administrator@.sthou.com is in each one.

is there a simple way thru sql or T-SQL to delete that "administrator@.sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?

thanks again for any easy answer
rikYou should be able to use the Replace function to delete that with an update query. It's available in both Access and T_SQL.|||Jelly Link update the post : It should be UPDATE, not DELETE
UPDATE <table_name>
set EmailTo = replace(EmailTo, 'administrator@.sthou.com;','')
where EmailTo like 'administrator@.sthou.com;%'

UPDATE <table_name>
set EmailTo = replace(EmailTo, ';administrator@.sthou.com;',';')
where EmailTo like '%;administrator@.sthou.com;%'

UPDATE <table_name>
set EmailTo = replace(EmailTo, ';administrator@.sthou.com','')
where EmailTo like '%;administrator@.sthou.com'|||delete <table_name>

delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.|||ups sorry.....its UPDATE :p update...set......where.........

ehm...i use the where clause to remove the separator ";" and the email address when administrator@.sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@.sthou.com (eg : blablaadministrator@.sthou.com)|||delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.

ups sorry.....its UPDATE :p update...set......where.........

ehm...i use the where clause to remove the separator ";" and the email address when administrator@.sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@.sthou.com (eg : blablaadministrator@.sthou.com)


Delete instead of update!!!!!!!!!.Stick this post and dont allow the poster to edit again in this post.hehehehe.lets every one see.I can see the panic in jelly link's face that time ,lol|||sorry.....hav so many things in my head :p|||it works great - i truly appreciate the help on this. Working with Oracle for so long, i feel like im running to catch up. it all looks familiar, but really not at all
thanks again
rik