SQL, conditional column values?

Question

One of the columns in my table is "DataSource". Datasource can have two values, lets say "A" or "B". I would like to always take the row when Datasource = "A", however, if there isn't an entry for A I would like to take "B".

How does one do this in SQL Server?

EDIT:

So for a partucular product (Product ID) one Product ID may have two rows, each containing a different DataSource, wheres another ProductID may only have one DataSource:

{ProductID DataSource}
{1  A},
{1  B},
{2  B}

Here I would wish to select the top and bottom row

Are you selecting just one record from the whole table? Or are trying to get multiple records, excluding B's when A's exist? And if so, do you have other identifying fields so the A and B records can be related to each other? (Give some example data showing which records to include and which to exclude) — MatBailie
– MatBailie, Commented Jan 18, 2012 at 12:59

MatBailie · Accepted Answer · 2012-01-18 13:17:49Z

2

A few options following your edit...

SELECT
  *
FROM
  table
WHERE
  DataSource = 'A'
  OR DataSource = 'B' AND NOT EXISTS (SELECT * FROM table AS lookup WHERE ProductID = table.ProductID AND DataSource = 'A')

SELECT
  *
FROM
  table
INNER JOIN
  (SELECT ProductID, MAX(DataSource) AS DataSource FROM table) AS lookup
    ON  lookup.ProductID  = table.ProductID
    AND lookup.DataSource = table.DataSource

WITH
  sequenced AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY DataSource) AS sequence_id
  FROM
    table
)
SELECT
  *
FROM
  sequenced
WHERE
  sequence_id = 1

answered Jan 18, 2012 at 13:17

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

Comments

aF. · Accepted Answer · 2012-01-18 13:13:46Z

1

select ProductID from product where DataSource= 'A'
UNION
select id from DataSource where product not in (select ProductID from product where DataSource = 'A')

edited Jan 18, 2012 at 13:13

answered Jan 18, 2012 at 12:52

aF.

67k45 gold badges141 silver badges201 bronze badges

2 Comments

Hans Keﬆing Over a year ago

If there are no DataSource='A' records, you don't need to exclude them in the second part ...

MatBailie Over a year ago

@HansKesting - This covers three scenarios. Products having only DataSourceA, only DataSourceB, or having both data sources. Both and only A are covered by the first query, but only B is covered by the second.

Oleg Dok · Accepted Answer · 2012-01-18 13:27:03Z

1

SELECT top 1 * 
from YourTable 
ORDER BY DataSource ASC

Else

if the real values are NOT 'A' or 'B', which are just placeholders of @A and @B variables

SELECT top 1 * 
from YourTable 
ORDER BY CASE DataSource WHEN @A THEN 0 ELSE 1 END ASC

or

if there more variances than AB

SELECT top 1 * 
from YourTable 
ORDER BY CASE DataSource WHEN @A THEN 0 WHEN @B THEN 1 ELSE 2 END ASC

OR

If you need to group by ProductId

select * from (
SELECT *,
ROW_NUMBER OVER(PARTITION BY ProductId ORDER BY CASE DataSource WHEN @A THEN 0 WHEN @B THEN 1 ELSE 2 END ASC) ordinal
from YourTable 
) t
WHERE t.Ordinal = 1

edited Jan 18, 2012 at 13:27

answered Jan 18, 2012 at 12:55

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

1 Comment

intrigued_66 Over a year ago

My A and B are actually string-values, can I still use the second method?

Ɖiamond ǤeezeƦ · Accepted Answer · 2012-01-18 13:19:12Z

0

Based on your sample data, this works:

SELECT ProductID, MIN(DataSource)
FROM @tab
GROUP BY ProductID;

And here is my test data:

declare @tab table (ProductID int, DataSource char(1))
insert into @tab values (1, 'A');
insert into @tab values (1, 'B');
insert into @tab values (2, 'B');

If there are more columns in the table than the two you show then:

SELECT T1.*
FROM @tab T1
JOIN
(
    SELECT ProductID, MIN(DataSource) AS DataSource
    FROM @tab
    GROUP BY ProductID
) T2 ON T1.ProductID = T2.ProductID AND T1.DataSource  = T2.DataSource

answered Jan 18, 2012 at 13:19

Ɖiamond ǤeezeƦ

3,3413 gold badges32 silver badges42 bronze badges

2 Comments

intrigued_66 Over a year ago

thanks, i will select this as the answer because of the use of Group by is helpful to me

MatBailie Over a year ago

Assumes no other fields exist in the table. (Which is the example given by the OP, so it works for that specific case.) To deal with other fields this is already embedded in the second option in my answer. [Following an edit, this now matches my second option]

Oleg Dok · Accepted Answer · 2012-01-18 13:29:49Z

0

ok, if I understood you requirement correctly then this would work

select TOP 1 * from yourtable where DATASOURCE = ISNULL(SELECT MAX(DATASOURCE) WHERE DATASOURCE="A","B")

The idea here is simple, you want to change DATASOURCE condition based on whether you have any rows of your first preference ("A") are available or not, if yes, then apply "A" condition or apply "B" condition

edited Jan 18, 2012 at 13:29

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

answered Jan 18, 2012 at 12:57

dejjub-AIS

1,5413 gold badges25 silver badges50 bronze badges

2 Comments

dejjub-AIS Over a year ago

why it is a bad answer? this works perfect (not sure the best/optimized query), but this answer doesnt deserve a negetive

MatBailie Over a year ago

I don't know who down voted, but... The answer doesn't use TOP 1, so it's obviously a set based approach. In a set of data, it seems likely that there is a FK and that some FKs have DataSourceA, some FK's have DataSourceB and some FKs have both. In your case if ANY have DataSourceA then NONE of the records with only DataSouceB are returned.

Collectives™ on Stack Overflow

SQL, conditional column values?

5 Answers 5

Comments

2 Comments

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related