Need Query for this logic - 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)

Need Query for this logic

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-05-04 : 12:32:04

I have the logic clear in my head but doing it with excel will take me a long time. Let me put out the logic here and you can tell me if you can do this quickly through sql queries.

i have data in sql table like this.


Name		Color
Gupta		Green
Smith		Blac
Smith		Red
Smith		Green
Traverso	White
Traverso	Yellow


Name		Color		Count
Gupta		Green		1
Smith		Blac		3
Smith		Red		3
Smith		Green		3
Traverso	White		2
Traverso	Yellow		2

Here’s what we need--
All the names with count 1 are fine as they are.
All the names with count equal to or greater than 2 need to be edited the following way:
If the names above contain ‘BLAC’ as one of the color, then leave it as is.
If not, just retain one of them(any of the record).
So in the above example, you will keep both instances of Smith, but you will only keep one instance of Traverso(either it is white or yellow).
How can we write a query for this ? plz let me know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:37:32

[code]
SELECT Name,
Color,
Count
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY NEWID()) AS Seq,COUNT(CASE WHEN Color='Blac' THEN 1 ELSE 0 END) OVER (PARTITION BY Name) AS Occur,*
FROM Table
)t
WHERE Occur>0
OR Seq=1
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources