Delete Statement

If I have a table like below. Does anyone know how I write a delete statemen
t
to delete the Timestamps which are the earliest whenever a FromURN is the
same?
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
1 100 400 1982-06-15 00:00:00.000 2005-07-28
15:24:29.217
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
15:24:29.217
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Want to end up with this.
NO FromURN ToURN MoveDateMerged Timestamp
-- -- -- --
--
24 100 400 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
15:26:21.480
Thanks for everyone who has helped me since I started this on Monday. I
really do appreciate all the help. I'm finally getting there.Best thing would be to post some ddl and sample data int he group, but it
would be something like:
Delete from sometable
Where st.no =
(
--getting the most recent of them
Select TOP 1 no from sometable st
INNER JOIN
(
--Avaluating all with more than one presence of FromURN
Select FromURN
From Sometable
Group by FromURN
HAVING COUNT(*) > 1
) subquery
ON subquery.FromURN = st.FromURN
Where st.no = sometable.no
order by Timestamp
)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" wrote:

> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||Hi Stephen
You can try this as:
DELETE <TableName>
FROM <TableName>
INNER JOIN
(
select fromURN, max(TimeStamp) TimeStamp
from <TableName> group by fromURN
)NewTab
On NewTab.fromURN = <TableName>.fromURN AND NewTab.TimeStamp =
<TableName>.TimeStamp
Just replace <TableName> with the name of your table. Please let me know if
you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Stephen" wrote:

> If I have a table like below. Does anyone know how I write a delete statem
ent
> to delete the Timestamps which are the earliest whenever a FromURN is the
> same?
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 1 100 400 1982-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
> 15:24:29.217
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
>
> Want to end up with this.
> NO FromURN ToURN MoveDateMerged Timestamp
> -- -- -- --
> --
> 24 100 400 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> 26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
> 15:26:21.480
> Thanks for everyone who has helped me since I started this on Monday. I
> really do appreciate all the help. I'm finally getting there.|||On Fri, 29 Jul 2005 02:20:03 -0700, Stephen wrote:

>If I have a table like below. Does anyone know how I write a delete stateme
nt
>to delete the Timestamps which are the earliest whenever a FromURN is the
>same?
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>1 100 400 1982-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>16 1700 1600 1983-06-15 00:00:00.000 2005-07-28
>15:24:29.217
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>
>Want to end up with this.
>NO FromURN ToURN MoveDateMerged Timestamp
>-- -- -- --
>--
>24 100 400 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>26 1700 16000 1983-06-15 00:00:00.000 2005-07-28
>15:26:21.480
>Thanks for everyone who has helped me since I started this on Monday. I
>really do appreciate all the help. I'm finally getting there.
Hi Stephen,
The solutions by Jens and Chandra will delete only the earliest row from
each set of duplicates. If three rows share the same FromURN, the
earliest is deleted and the other two are retained.
If you actually wanted to delete all but the latest row (i.e. in the
example above, delete the two earliest rows), try this:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.FromURN = MyTable.FromURN
AND b.Timestamp > MyTable.Timestamp)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Stephen,
You can try this
Delete from yourTable where NO =
(
select min(NO) FROM YourTable group by
fromURN,convert(varchar,timestamp,112)
)
I Hope this help
With warm regards
Jatinder Singh|||Hi Stephen,
Typo please replace = with in .
Sorry
With warm regards
Jatinder Singh