Conditional removal of rows with a duplicate colum

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)

Conditional removal of rows with a duplicate colum

Author

Topic

bhiers
Starting Member

2 Posts

Posted - 2008-11-18 : 17:40:17

Example data

ID Name Owner
1 Wheel Global
2 Wheel Bill
3 Rim Global
4 Bolt Global

I have to return all database but where column "Name" has duplicates I only want to return data where the owner is not Global

ID Name Owner
2 Wheel Bill
3 Rim Global
4 Bolt Global

I know its really simple but I'm just missing something.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 17:56:03

select a.[ID],a.[Name],b.[Owner] from
(select [id],[name] from <yourtable> group by [id],[name] having count(*)>1) a
join <yourtable> b on a.[id]=b.[id] and b.[owner]<>'global'
union all
select a.[ID],a.[Name],b.[Owner] from
(select [id],[name] from <yourtable> group by [id],[name] having count(*)=1) a
join <yourtable> b on a.[id]=b.[id]

bhiers
Starting Member

2 Posts

Posted - 2008-11-19 : 08:34:45

That didn't work the first part of the statement will never return anything select a.[ID],a.[Name],b.[Owner] from
(select [id],[name] from <yourtable> group by [id],[name] having count(*)>1) a
join <yourtable> b on a.[id]=b.[id] and b.[owner]<>'global'

because the ID is never going to be duplicated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 08:44:04

SELECT ID, Name, Owner
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY CASE WHEN Owner='Global' THEN 'ZZZZZZZZZZ' ELSE Owner END) AS Seq,
COUNT(ID) OVER (PARTITION BY Name) AS Total
FROM YourTable
)t
WHERE Seq<>Total
OR Total=1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 08:51:50

[code]SELECT ID,
Name,
Owner
FROM (
SELECT ID,
Name,
Owner,
COUNT(*) OVER (PARTITION BY Name) AS cnt
FROM YourTable
) AS d
WHERE cnt = 1
OR Owner <> 'Global'[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 08:56:21

[code]SELECT s.*
FROM @Sample AS s
LEFT JOIN (
SELECT Name,
MIN(CASE WHEN Owner = 'Global' THEN ID ELSE NULL END) AS ID
FROM @Sample
GROUP BY Name
HAVING COUNT(*) > 1
) AS d ON d.ID = s.ID
WHERE d.ID IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources