As an extension to my previous post

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

As an extension to my previous post

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 15:45:10

So in this post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=174265
We (I) was shown how to update the value of my COUNTS column to show the correct number of FNS's per Arrestno in that table. There is a "chance" that i may need to reduce the number of Arrestno's per FSN in that table to one entry each, now that the COUNTS column is accurately reflecting things.

For example, if i have:


ARRESTNO	FSN	        Counts
GCSO00ARR053680	539.0018b8a	3
GCSO00ARR053680	539.0018b8a	3
GCSO00ARR053680	539.0018b8a	3
GCSO00ARR053680	810023b	        1
GCSO00ARR053680	812.014 2c	3
GCSO00ARR053680	812.014 2c	3
GCSO00ARR053680	812.014 2c	3

And i want to reduce it to:


ARRESTNO	FSN	        Counts
GCSO00ARR053680	539.0018b8a	3
GCSO00ARR053680	810023b	        1
GCSO00ARR053680	812.014 2c	3

What's the best way to do so across the board?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 15:47:35

Will the following leave one row for each Arrestno/FSN where the FSN is unique?


Delete D FROM
(
SELECT Arrestno, FSN, ROW_NUMBER()OVER(ORDER BY Arrestno, FSN) AS RowNum 
FROM dbo.arr_afss)D
JOIN
(
SELECT Arrestno, FSN, ROW_NUMBER()OVER(ORDER BY Arrestno, FSN) AS RowNum 
FROM dbo.arr_afss)E

on D.arrestno = E.arrestno
AND D.RowNum < E.RowNum
AND D.FSN=e.FSN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:57:55

[code]
DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ARRESTNO, FSN, Counts ORDER BY (SELECT 1) ) AS Rn
FROM table
)t
WHERE Rn>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 15:59:21

You're sure about this? I'm at the tail end of this conversion. If i nuke my data at this point, i'm going to cry.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 16:03:21

quote:
Originally posted by WJHamel

You're sure about this? I'm at the tail end of this conversion. If i nuke my data at this point, i'm going to cry.

first put a select intead of DELETE and see for yourself. As per your posted data it should work. So if you're seeing the duplicate data you can go ahead and replace SELECT with DELETE to do actual deletion


SELECT * FROM
--DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ARRESTNO, FSN, Counts ORDER BY (SELECT 1) ) AS Rn
,*
FROM table
)t
WHERE Rn>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 16:07:28

when i change it from a Delete t from to a select * from, my resultset looks like this:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 16:12:24

quote:
Originally posted by WJHamel

when i change it from a Delete t from to a select * from, my resultset looks like this:

Rn
2
2
2
2
2
2
2
3
4
5

you've not used my latest posted suggestion. please copy paste and run it (and note the change i did from my first suggestion


SELECT * FROM
--DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY ARRESTNO, FSN, Counts ORDER BY (SELECT 1) ) AS Rn
,*
FROM table
)t
WHERE Rn>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 16:17:04

that's better. I'll put this one in the can if in fact i need to delete the extra rows. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 16:19:06

quote:
Originally posted by WJHamel

that's better. I'll put this one in the can if in fact i need to delete the extra rows. thanks.

First validate and then apply DELETE
It should work fine so far as given sample data was exact representation of your business scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources