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       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：

ggordon's answer will work well enough, but just since a window function isn't strictly necessary I figured I'd pass another solution:

select distinct 
a.col1,
ifnull(b.col2, 'no') col2

from my_table a
left join (
    select distinct
    col1,
    col2
    from my_table

    where col2 = 'yes'
  ) b on a.col1 = b.col1

Output:

| col1 | col2 |
| ---- | ---- |
| a    | yes  |
| b    | no   |
| c    | yes  |
| d    | yes  |

CodePudding user response：

You may try the following:

Approach 1

You may use row_number to retrieve a row number ordered by col2 in descending order that may be used to filter your results eg.

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

Approach 2

or simply use a group by col1 with the MAX function. The group by will ensure that for each col1 value you will receive the MAX of col2 that is yes if available and no if not.

SELECT
     col1,
     MAX(col2) as col2
FROM
    my_table
GROUP BY
    col1;

View working demo on DB Fiddle