Select rows and union two columns to one except certain value

All we need is an easy explanation of the problem, so here it is.

I have a three column table

CREATE TABLE "colls" (
    "w1"    TEXT,
    "w2"    TEXT,
    "coocc_freq"    INTEGER
)
w1 w2 coocc_freq
s o 2
o d 10
a q 8
w a 8
s w 14
a s 20

I just want to get w1 and w2 with s value:

SELECT * FROM colls where w1='s' or w2='s' order by coocc_freq desc
w1 w2 coocc_freq
a s 20
s w 14
s o 2

How I can to keep just two columns? w1 + w2 – s = w

I.e I want to skip s value and get something like:

w coocc_freq
a 20
w 14
o 2

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

SELECT COALESCE(NULLIF(w1, 's'), w2) AS w
       coocc_freq
FROM colls 
WHERE 's' IN (w1, w2);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply