difficult delete solution for t-sql - who can help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

difficult delete solution for t-sql - who can help

Author

Topic

landau66
Yak Posting Veteran

61 Posts

Posted - 2008-08-22 : 06:47:28

hello everyone!

I have a table with stockprices per second:

id, sDatetime, price

very seldomly i have a doulbe row which i can see by a double entry in the column sDatetime. I filter this double entries out by:

select sdatetime from testen where sdatetime =
(select sdatetime from testen group by sdatetime having count(sdatetime) >1)

result would be:
2008-03-26 14:11:17.000
2008-03-26 14:11:17.000

or
2008-03-26 14:11:17.000
2008-03-26 14:11:17.000
2008-03-26 14:11:17.000

if i would have three times the same entry

top1 i want to keep, the other entries i want to delete. how is that possible without a loop or cursor????

data:
16525 2008-03-26 14:11:00.000 6510.71
16526 2008-03-26 14:11:07.000 6510.66
16527 2008-03-26 14:11:08.000 6510.66
16643 2008-03-26 14:11:09.000 6510.70
16640 2008-03-26 14:11:10.000 6510.69
16534 2008-03-26 14:11:15.000 6510.73
16535 2008-03-26 14:11:16.000 6510.69
16536 2008-03-26 14:11:17.000 6510.69
16538 2008-03-26 14:11:17.000 6510.82
16541 2008-03-26 14:11:17.000 6510.72
16542 2008-03-26 14:11:23.000 6510.83
16642 2008-03-26 14:11:26.000 6510.80
16547 2008-03-26 14:11:28.000 6510.83
16548 2008-03-26 14:11:29.000 6510.83

many thanks in advance and greetings from vienna

landau

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-22 : 06:56:19

What version of SQL?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-22 : 07:13:45

If using sql server 2005,
declare @TableVar table (
ID int identity(1,1),
sdatetime datetime )

Insert @TableVar

Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:15.000' union all
Select '2008-03-26 14:11:15.000'
Select * from @TableVar
DELETE tbl From
(
select ROW_NUMBER() OVER(PARTITION BY sdatetime ORDER BY sdatetime DESC) AS 'ID',sdatetime
from @TableVar where ID IN(1,2,3) ) tbl where ID<>1

Select * from @TableVar

Hope this is what you want. Above will delete only if there are exactly 3 duplicate entries.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 07:27:06

quote:
Originally posted by sunil

If using sql server 2005,
declare @TableVar table (
ID int identity(1,1),
sdatetime datetime )

Insert @TableVar

Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:15.000' union all
Select '2008-03-26 14:11:15.000'
Select * from @TableVar
DELETE tbl From
(
select ROW_NUMBER() OVER(PARTITION BY sdatetime ORDER BY sdatetime DESC) AS 'ID',sdatetime
from @TableVar where ID IN(1,2,3) ) tbl where ID<>1

Select * from @TableVar

Hope this is what you want. Above will delete only if there are exactly 3 duplicate entries.

You cant use alias directly like this.I dont think you really require the where.The outside condition is enough.

landau66
Yak Posting Veteran

61 Posts

Posted - 2008-08-22 : 07:27:40

i use sql server 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 07:29:47

or use this if sql 2000

DELETE t
FROm YourTable t
INNER JOIN (SELECT MAX(id) AS MaxID,sDatetime
            FROM YourTable
            GROUP BY sDatetime)tmp
ON tmp.sDatetime=t.sDatetime
AND tmp.MaxID=t.id

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-22 : 07:35:43

quote:
Originally posted by visakh16

quote:
Originally posted by sunil

If using sql server 2005,
declare @TableVar table (
ID int identity(1,1),
sdatetime datetime )

Insert @TableVar

Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:15.000' union all
Select '2008-03-26 14:11:15.000'
Select * from @TableVar
DELETE tbl From
(
select ROW_NUMBER() OVER(PARTITION BY sdatetime ORDER BY sdatetime DESC) AS 'ID',sdatetime
from @TableVar where ID IN(1,2,3) ) tbl where ID<>1

Select * from @TableVar
Hope this is what you want. Above will delete only if there are exactly 3 duplicate entries.

You cant use alias directly like this.I dont think you really require the where.The outside condition is enough.

I have put where condition as only duplicate records with 3 entries should be deleted. I thought O/P doesnot want record with 2 entries to be deleted.Also, can yo please elaborate on You cant use alias directly.
In your query, should n't tmp.MaxID=t.id be tmp.MaxID<>t.id as = condition will delete top most record.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-22 : 09:24:08

quote:
Originally posted by sunil

quote:
Originally posted by visakh16

quote:
Originally posted by sunil

If using sql server 2005,
declare @TableVar table (
ID int identity(1,1),
sdatetime datetime )

Insert @TableVar

Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:15.000' union all
Select '2008-03-26 14:11:15.000'
Select * from @TableVar
DELETE tbl From
(
select ROW_NUMBER() OVER(PARTITION BY sdatetime ORDER BY sdatetime DESC) AS 'ID',sdatetime
from @TableVar where ID IN(1,2,3) ) tbl where ID<>1

Select * from @TableVar
Hope this is what you want. Above will delete only if there are exactly 3 duplicate entries.

You cant use alias directly like this.I dont think you really require the where.The outside condition is enough.

I have put where condition as only duplicate records with 3 entries should be deleted. I thought O/P doesnot want record with 2 entries to be deleted.Also, can yo please elaborate on You cant use alias directly.
In your query, should n't tmp.MaxID=t.id be tmp.MaxID<>t.id as = condition will delete top most record.

By ID in where clause what did you mean?
ID column or ID of row_number?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 09:47:02

quote:
Originally posted by sunil

quote:
Originally posted by visakh16

quote:
Originally posted by sunil

If using sql server 2005,
declare @TableVar table (
ID int identity(1,1),
sdatetime datetime )

Insert @TableVar

Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:15.000' union all
Select '2008-03-26 14:11:15.000'
Select * from @TableVar
DELETE tbl From
(
select ROW_NUMBER() OVER(PARTITION BY sdatetime ORDER BY sdatetime DESC) AS 'ID',sdatetime
from @TableVar where ID IN(1,2,3) ) tbl where ID<>1

Select * from @TableVar
Hope this is what you want. Above will delete only if there are exactly 3 duplicate entries.

You cant use alias directly like this.I dont think you really require the where.The outside condition is enough.

I have put where condition as only duplicate records with 3 entries should be deleted. I thought O/P doesnot want record with 2 entries to be deleted.Also, can yo please elaborate on You cant use alias directly.
In your query, should n't tmp.MaxID=t.id be tmp.MaxID<>t.id as = condition will delete top most record.

yup... just read earlier post once more. i think what OP needs is this

DELETE t
FROm YourTable t
LEFT JOIN (SELECT MAX(id) AS MaxID,sDatetime
            FROM YourTable
            GROUP BY sDatetime)tmp
ON tmp.sDatetime=t.sDatetime
AND tmp.MaxID=t.id
WHERE tmp.MaxID IS NULL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-22 : 09:48:03

You could put a unique constraint (or primary key) on the table to prevent dupes from getting in in the first place

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 09:48:50

quote:
Originally posted by sunil

quote:
Originally posted by visakh16

quote:
Originally posted by sunil

If using sql server 2005,
declare @TableVar table (
ID int identity(1,1),
sdatetime datetime )

Insert @TableVar

Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:17.000' union all
Select '2008-03-26 14:11:15.000' union all
Select '2008-03-26 14:11:15.000'
Select * from @TableVar
DELETE tbl From
(
select ROW_NUMBER() OVER(PARTITION BY sdatetime ORDER BY sdatetime DESC) AS 'ID',sdatetime
from @TableVar where ID IN(1,2,3) ) tbl where ID<>1

Select * from @TableVar
Hope this is what you want. Above will delete only if there are exactly 3 duplicate entries.

You cant use alias directly like this.I dont think you really require the where.The outside condition is enough.

I have put where condition as only duplicate records with 3 entries should be deleted. I thought O/P doesnot want record with 2 entries to be deleted.Also, can yo please elaborate on You cant use alias directly.
In your query, should n't tmp.MaxID=t.id be tmp.MaxID<>t.id as = condition will delete top most record.

What i meant was you cant use ID IN(1,2,3) if ID is actually the alias created in select list like below
ROW_NUMBER() OVER(PARTITION BY sdatetime ORDER BY sdatetime DESC) AS 'ID'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources