CODEWITHSUNDEEP
sqlsql-server
intrigued_66
intrigued_66

Reputation: 17220

SQL, conditional column values?

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

Upvotes: 3

Views: 2058

Answers (5)

dejjub-AIS
dejjub-AIS

Reputation: 1541

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

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21756

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

Upvotes: 1

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

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

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 2

aF.
aF.

Reputation: 66687

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

Upvotes: 1

Related Questions