SQL with conditional logic

Question

So I have a query that currently returns a table of 1s and 0s like the following

However, I would like to modify my query so that for rows with multiple 1s, we only keep the first 1 in the row. For example, I want to convert (1 1 1) to (1 0 0), (0 1 1) to (0 1 0), and so forth. It seems like IF logic would be a last resort.

What would you all recommend? Could I do a case statement where the value in one column depends on another columns value?

In other words, "COLUMN 2 = CASE WHEN COLUMN 1 = 1 and COLUMN 2 = 1, then 0...etc?"

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it — Taryn
– Taryn, Commented Mar 19, 2013 at 15:58

Gordon Linoff · Accepted Answer · 2013-03-19 16:02:37Z

2

For three columns, the logic is:

select col1,
       (case when col1 = 1 then 0 else col2 end) as col2,
       (case when col1 = 1 or col2 = 1 then 0 else col3 end) as col3
from query q

If you had more columns, I would take a different approach:

select col1,
       (case when firstOne < 2 then 0 else col2 end) as col2,
       (case when firstOne < 3 then 0 else col3 end) as col3,
       (case when firstOne < 4 then 0 else col4 end) as col4,
       . . .
from (select q.*,
             (case when col1 = 1 then 1
                   when col2 = 1 then 2
                   when col3 = 1 then 3
                   . . .
              end) as FirstOne
      from query q
     ) q

answered Mar 19, 2013 at 16:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

user359040 · Accepted Answer · 2013-03-19 18:19:28Z

0

As an alternative to conditional constructs, you could do something like:

select a, b * (1-a) as [b'], c * (1-b) * (1-a) as [c'] 
from your_table

This does depend on columns a, b and c each being allowed the values 0 and 1 only.

(Example here.)

answered Mar 19, 2013 at 18:19

user359040

Collectives™ on Stack Overflow

SQL with conditional logic

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related