Very Tricky Delete Statement - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Very Tricky Delete Statement

Author

Topic

thedryden
Starting Member

23 Posts

Posted - 2008-04-29 : 15:08:56

I have a rather tricky SQL delete query I’m trying to perform, preferably without cursors. I have the following data set, where I want each of the first changes to the status.

ID Status
1 Good
2 So-So
3 So-So
4 Bad
5 Bad
6 Bad
7 Bad
8 Bad
9 So-So
10 Good

So given the above data set I would want to delete the following IDs: 3, 5, 6, 7, and 8. As they not the first in order. The problem is that while I can get isolate and save first instance of each status (in this case 1, 2, and 4), I can’t get the what I want. Is there a SQL way of accomplishing this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 15:15:40

DELETE FROM YourTable WHERE ID NOT IN (SELECT MIN(ID) FROM YourTable GROUP BY Status)

Why don't you also want to delete 9,10?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-29 : 15:17:03

delete yourTable
where id not in (Select to get first ids here)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

thedryden
Starting Member

23 Posts

Posted - 2008-04-29 : 15:49:06

This is a portion of the columns for a larger event table, which include a whole host of changes (many of which don't actually change the status). What I want is a table that contains a row for every status change and the timestamp.

Correct me if I'm wrong though, wont both of the above examples delete everything but the first occurrence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 15:53:48

Yes as that is what you have shown in your first post. If this is not what you want, then you need to provide a better example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thedryden
Starting Member

23 Posts

Posted - 2008-04-29 : 16:36:14

I'm sorry I'm never very good at explaining these sorts of things. Given the table above, I want the table below:

ID Status
1 Good
2 So-So
4 Bad
9 So-So
10 Good

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 16:38:31

You need to describe what the business requirement is for keeping 9 and 10. Is it because it isn't grouped with the other ones?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thedryden
Starting Member

23 Posts

Posted - 2008-04-29 : 16:49:18

The goal is to have a record of every time the status changed from one status to another. To put it another way, if the ID above the current ID has a status diffrent that the current ID keep that ID, otherwise delete it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-29 : 18:29:34

[code]DECLARE @Yak TABLE (ID INT, Stat VARCHAR(5))
INSERT @Yak
SELECT 1, 'Good'
UNION ALL SELECT 2, 'So-So'
UNION ALL SELECT 3, 'So-So'
UNION ALL SELECT 4, 'Bad'
UNION ALL SELECT 5, 'Bad'
UNION ALL SELECT 6, 'Bad'
UNION ALL SELECT 7, 'Bad'
UNION ALL SELECT 8, 'Bad'
UNION ALL SELECT 9, 'So-So'
UNION ALL SELECT 10, 'Good'
UNION ALL SELECT 11, 'Good'
UNION ALL SELECT 12, 'So-So'
UNION ALL SELECT 13, 'So-So'
UNION ALL SELECT 14, 'Bad'
UNION ALL SELECT 15, 'Bad'
UNION ALL SELECT 16, 'Bad'
UNION ALL SELECT 17, 'Bad'
UNION ALL SELECT 18, 'Bad'
UNION ALL SELECT 19, 'So-So'
UNION ALL SELECT 20, 'Good'

DELETE
A
FROM
@Yak AS A
LEFT OUTER JOIN
@Yak AS B
ON A.ID = B.ID + 1
AND A.Stat = B.Stat
WHERE
B.ID IS NOT NULL

SELECT *
FROM @Yak[/code]

thedryden
Starting Member

23 Posts

Posted - 2008-04-29 : 19:00:53

I like it, simple but very functional. I'll have to use a Row_Number command to get this to work properly, but it should work!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-30 : 15:12:08

If it's not too late, here's another way that doesn't require ROW_NUMBER() or sequential id's:

DECLARE @Yak TABLE (ID INT, Stat VARCHAR(5))
INSERT @Yak
SELECT 1, 'Good'
UNION ALL SELECT 2, 'So-So'
UNION ALL SELECT 3, 'So-So'
UNION ALL SELECT 4, 'Bad'
UNION ALL SELECT 5, 'Bad'
UNION ALL SELECT 6, 'Bad'
UNION ALL SELECT 7, 'Bad'
UNION ALL SELECT 8, 'Bad'
UNION ALL SELECT 9, 'So-So'
UNION ALL SELECT 10, 'Good'
UNION ALL SELECT 11, 'Good'
UNION ALL SELECT 12, 'So-So'
UNION ALL SELECT 13, 'So-So'
UNION ALL SELECT 14, 'Bad'
UNION ALL SELECT 15, 'Bad'
UNION ALL SELECT 16, 'Bad'
UNION ALL SELECT 17, 'Bad'
UNION ALL SELECT 18, 'Bad'
UNION ALL SELECT 19, 'So-So'
UNION ALL SELECT 20, 'Good'

DELETE A
FROM
@Yak AS A
CROSS APPLY
(select top 1 ID,Stat from @Yak B where a.ID > b.ID order by b.ID DESC) B
WHERE B.Stat = A.Stat

SELECT *
FROM @Yak

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources