Removing 2nd row with a certain value

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

Removing 2nd row with a certain value

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-12-14 : 17:50:21

Hello,

I have this table with 3 columns:
StationID int, CType_Code char(1), CType_Status varchar(20)

In the table, for each Station and CType_Code pair - I want only one status. But because of the way the other tables were structured - we can have 2 records for some Station/CType_Code pairs. Like:

StationID CType CType_Status
1028 ----- 1 ---- Active
1028 ----- 1 ---- Suspended

I want to remove the 2nd column with value 'Suspended' from each Station with 2 records.

Need help, please.
Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-14 : 17:55:20

Is it always Active and possible Suspended? If so, to select

SELECT * FROM YourTable WHERE CType_Status = 'Active';

If you want to permanently delete rows wehre CType_Status is "Suspended"

DELETE FROM YourTable WHERE CType_Status = 'Suspended';

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-12-14 : 18:27:06

No - it's not that simple.
Sorry if I wasn't clear enough. There are other Stations with 'Suspended' status. I don't want to do anything with them.
I only want to remove the row with 'Suspended' for the Station/CType_Code pairs that have 2 rows instead of 1.

StationID CType CType_Status
1028 ----- 1 ---- Active
1028 ----- 1 ---- Suspended
1028 ----- 2 ---- Suspended
1029 ----- 1 ---- Suspended
1030 ----- 3 ---- Active
1030 ----- 3 ---- Suspended

In the above example, I don't want to remove the row "1028 / 2 / Suspended", or "1029 / 1 / Suspended",
but I do want to remove "1028 / 1 / Suspended" and "1030 / 3 / Suspended".
Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-12-14 : 19:00:00

[CODE];With DupStationCType
as (
select
StationID,
CType
from YourTable
group by
StationID,
CType
having count(*) > 1
)
delete yt
from YourTable yt
inner join
DupStationCType dups
on yt.StationID = dups.StationID
and yt.CType = dups.CType
where
yt.CType_Status = 'Suspended'[/CODE]

=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-12-14 : 19:14:09

I'll try it and let you know Monday - Bustaz.
Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-15 : 00:07:14

[code]
Delete F from
(
select *,ROW_NUMBER() OVER (PARTITION BY StationId,Ctype Order by Case When Ctype_Status = 'Suspended' then 1 else 2 End) as Seq,
COUNT()OVER (PARTITION BY StationId,Ctype) CNT
from DupStationCType
)F
Where F.Seq = 1 and F.CNT > 1[/code]

sqlbug
Posting Yak Master

201 Posts

Posted - 2012-12-17 : 11:38:07

Thanks Bustaz and sodeep - it worked. Man - these queries look pretty complicated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources