SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Query Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 07/09/2013 :  16:16:23  Show Profile  Reply with Quote
Hi All !!! I have the following table:

SKU    PRODUCT_COLOR

123456 Blue 
123456 Red

123457 Red

123458 Black
123458 Red

I need a query that will output the SKU:

(1) Instances where red is the only color.
AND
(2) If there are multiple colours, it will exclude red.

The output would be as follows:

123456 Blue 
123457 Red
123458 Black

I'm not quite sure how to do this, Any ideas? thanKS!

SELECT SKU, PRODUCT_COLOR FROM TABLE T1

Edited by - funk.phenomena on 07/09/2013 16:28:09

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 07/09/2013 :  16:45:15  Show Profile  Reply with Quote
Here is one way to do this. If you have duplicates, for exmple adding another row of "123456 Red" to your sample data will break this. In that case, what do you want to get? Or, if you add antoehr row of "123457 Red" what should be the output?
SELECT * FROM
(
	SELECT
		sku, 
		product_color,
		COUNT(*) OVER (PARTITION BY sku) AS N
	FROM
		YourTable
) s
WHERE N=1 OR product_color <> 'Red';
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

422 Posts

Posted - 07/09/2013 :  18:57:38  Show Profile  Reply with Quote
Alternative solution:

select t1.sku
      ,t1.product_color
  from table as t1
       inner join (select sku
                         ,sum(case when product_color='Red' then 0 else 1 end) as nonreds
                     from table
                    group by sku
                  ) as t2
               on t2.sku=t1.sku
 where t2.nonreds=0
    or t1.product_color<>'Red'

ps.: Syntax errors might occur as I don't have access to database right now.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/10/2013 :  03:31:54  Show Profile  Reply with Quote

SELECT sku,product_color 
FROM
(
	SELECT
		sku, 
		product_color,
		DENSE_RANK() OVER (PARTITION BY sku ORDER BY CASE WHEN product_color = 'Red' THEN 1 ELSE 0 END) AS N
	FROM
		YourTable
) s
WHERE N=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000