Something like COALESCE for complicated query?

Question

Suppose I have a table1:

column1|column2|state|
-------|-------|-----|
test1  |      2|    0|
test1  |      3|    0|
test1  |      1|    1|
test2  |      2|    1|
test2  |      1|    2|

I want to select (actually delete, but I use select for testing) all columns that don't have unique column1 and don't select (actually retain) only the rows that have:

1. state = 0 and smallest value in column2,
2. if no row with state = 0 exists, then the row with just smallest value in column2.

So the result if the select should be:

column1|column2|state|
-------|-------|-----|
test1  |      3|    0|
test1  |      1|    1|
test2  |      2|    1|

and the retained rows (in case of delete) should be:

column1|column2|state|
-------|-------|-----|
test1  |      2|    0|
test2  |      1|    2|

I tried to achieve it with following (which does not work):

SELECT * FROM table1 AS result1
    WHERE
        result1.column1 IN
        (SELECT
            result2.column1
        FROM
            table1 AS result2
        WHERE /*part that works*/)
    AND
        result1.column2 >
        (SELECT
            min(result3.column2)
        FROM
            table1 AS result3
        WHERE (COALESCE(
            result3.column1 = result1.column1 
        AND
            result3.state = 0,

        WHERE
            result3.column1 = result1.column1 
        )))

The part that I can't figure out is behind result1.column2 >.
I want to compare the result1.column2 with the result of

1. smallest value from result-set where it3.state = 0,
2. if 1. does not exist, then with smallest value from similar result-set without it3.state = 0 condition.

That is my problem, I hope it makes sense. Maybe it can be rewritten in a more efficient/neater way completely.
Can you help me to fix that query?

Answer 1

Is this what you want?

SELECT
        *
  FROM
        table1 AS result1
 WHERE
        result1.column1 IN (SELECT result2.column1
                              FROM table1 AS result2
                             WHERE /*part that works*/)
   AND  result1.column2 > COALESCE( ( SELECT min(result3.column2)
                                        FROM table1 AS result3
                                       WHERE result3.column1 = result1.column1 
                                         AND result3.state = 0 )
                                   ,( SELECT min(result3.column2)
                                        FROM table1 AS result3
                                       WHERE result3.column1 = result1.column1  )
                                  )

;