En primer lugar, el título de esta pregunta es horrible, pero no encontré una mejor manera de describir mi problema.

Probablemente hay una manera muy fácil de hacer esto, pero no pude entenderlo. Esto es muy similar aesta pregunta, pero estoy ejecutando en sqlite3 (iOS), así que sospecho que mis opciones son mucho más limitadas.

Tengo una mesa con registros de productos. Todos los registros tienen una identificación (nota: no estoy hablando de la identificación de la fila, sino de un número de identificación único para cada producto). Algunos productos pueden tener dos entradas en la tabla (ambas con el mismo ID). La única diferencia sería en una columna especial (digamos que la columna COLOR puede ser ROJA o VERDE).

Lo que quiero hacer es crear una lista de productos únicos basados ​​en el valor de COLOR, con prioridad a VERDE si existen registros VERDE y ROJO para el mismo producto.

En definitiva, si tengo el siguiente caso:

id       PRODUCT ID    COLOUR
1        1001          GREEN
2        1002          GREEN
3        1002          RED
4        1003          RED

Me gustaría que mi SELECT devuelva las filas 1, 2 y 4. ¿Cómo puedo lograr esto?

Mi enfoque actual es tener tablas separadas y hacer la unión manualmente, pero obviamente esta es una muy mala idea.

Nota: He intentado utilizar el mismo enfoque deaquí:

SELECT * 
  FROM xx
 WHERE f_COLOUR = "GREEN"
UNION ALL
SELECT * 
  FROM xx 
 WHERE id not in (SELECT distinct id 
                    FROM xx 
                   WHERE f_COLOUR = "GREEN");

Pero el resultado que obtengo es filas 1,2,3,4 en lugar de solo 1,2,4. ¿Qué me estoy perdiendo?

Editar: Una pregunta más, por favor: ¿cómo se puede hacer que esto funcione con un subconjunto de registros, es decir? Si en lugar de toda la tabla quisiera filtrar algunos registros?

Por ejemplo, si tuviera algo como SELECT * FROM table WHERE product LIKE "1%" ... ¿cómo puedo recuperar cada producto único, pero respetando la prioridad de color (VERDE> ROJO)?