Simple Query Question - 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

General SQL Server Forums

New to SQL Server Programming

Simple Query Question

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-07-09 : 16:16:23

Hi All !!! I have the following table:
[CODE]
SKU PRODUCT_COLOR

123456 Blue
123456 Red

123457 Red

123458 Black
123458 Red
[/CODE]
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:
[CODE]
123456 Blue
123457 Red
123458 Black
[/CODE]
I'm not quite sure how to do this, Any ideas? thanKS!
[CODE]
SELECT SKU, PRODUCT_COLOR FROM TABLE T1
[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-09 : 16:45:15

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';

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-07-09 : 18:57:38

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 03:31:54

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources