Select query elimitateing unwanted rows-CodePudding

I'm new to SQLITE and I am having trouble finding the solution.

I have TABLE1 with columns col1 and col2

col1---col2

a--------no

a--------yes

b--------no

c--------yes

c--------no

d--------yes

I want no repetitions from col1 but prioritize col2 when having "yes"

I want something like this

col1 --- col2

a--------yes

b--------no

c--------yes

d--------yes

CodePudding user response：

You will first want to do a distinct select on column one. Then you will want to make a case statement which is essentially a if statement in other languages. The case needs to be if column 1 is yes return it. if it is not yes then return no. It would look something like this

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;

CodePudding user response：

You may use row_number ordered by col2 in descending order to achieve this eg.

SELECT
    col1,
    col2
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY col1
            ORDER BY col2 DESC
        ) rn
    FROM
        my_table
) t
WHERE rn=1;

col1	col2
a	yes
b	no
c	yes
d	yes

View working demo on DB Fiddle