Delete Statement

29 Jul 2005 9:20 AM

Stephen

If I have a table like below. Does anyone know how I write a delete statement
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.

29 Jul 2005 10:36 AM

Jens SÃ¼ÃŸmeyer

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
---

Show quote

"Stephen" wrote:

> If I have a table like below. Does anyone know how I write a delete statement
> 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.

29 Jul 2005 11:42 AM

Chandra

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/
---------------------------------------

Show quote

"Stephen" wrote:

> If I have a table like below. Does anyone know how I write a delete statement
> 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.

29 Jul 2005 10:35 PM

Hugo Kornelis

On Fri, 29 Jul 2005 02:20:03 -0700, Stephen wrote:

Show quote

>If I have a table like below. Does anyone know how I write a delete statement
>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)

1 Aug 2005 6:21 AM

jsfromynr

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

1 Aug 2005 7:33 AM

jsfromynr

Hi Stephen,
Typo please replace = with in .
Sorry

With warm regards
Jatinder Singh