Conditional SQL Select statement

Question

I have a table below.

Name          Product
---------------------
Value          2002
HigherLimit    *
HigherLimit    2002
LowerLimit     *

I need a select statement to return distinct(Name) with the following condition.

If a Product is 2002 then return '2002' else return '*'

Here is the expected result

Name          Product
---------------------
Value          2002
HigherLimit    2002
LowerLimit     *

Any help would be greatly appreciated.

Thanks Niju

Is it distinct name and product, if not is it if any of the names have a product use 2002, if not use *? And what is your DB server? — Nix
– Nix, Commented Jan 9, 2013 at 2:47
Only distinct name, distinct product is inserted per name. IT is SQL 2008 R2 — niju
– niju, Commented Jan 9, 2013 at 2:49

Michael Berkowski · Accepted Answer · 2013-01-09 02:54:02Z

2

Since 2002 will evaluate as greater than *, you can simply use a MAX() aggregate grouped by Name:

SELECT
  Name,
  MAX(Product) AS Product
FROM yourtable
/* WHERE Product IN ('2002', '*') -- if necessary */
GROUP BY Name
ORDER BY Product DESC

http://sqlfiddle.com/#!2/8da48/2

However, if your real table has more values than just 2002 or *, and you want to return the 2002 values rather than the greatest number, use a CASE to separate out the value you want (2002) and return all others as *. The MAX() aggregate is applied in the same way though:

SELECT
  Name,
  /* Force *all* values not 2002 to be `*` */
  MAX(CASE WHEN Product = '2002' THEN Product ELSE '*' END) AS Product
FROM yourtable
GROUP BY Name
ORDER BY Product DESC

http://sqlfiddle.com/#!2/f03af/2

edited Jan 9, 2013 at 2:54

answered Jan 9, 2013 at 2:48

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional SQL Select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related