Select rows and union two columns to one except certain value

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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


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 Comment