Primeiro de tudo, o título desta pergunta é horrível, mas não encontrei uma maneira melhor de descrever meu problema.

Há provavelmente uma maneira muito fácil de fazer isso, mas não consegui descobrir. Isso é muito parecido comisto pergunta, mas eu estou rodando no sqlite3 (iOS), então eu suspeito que minhas opções são muito mais limitadas.

Eu tenho uma tabela com registros de produtos. Todos os registros têm um ID (nota: não estou falando sobre o ID da linha, mas sim um número de identificação exclusivo para cada produto). Alguns produtos podem ter duas entradas na tabela (ambas com o mesmo ID). A única diferença seria em uma coluna especial (digamos que a coluna COLOR pode ser VERMELHA ou VERDE).

O que eu quero fazer é criar uma lista de produtos exclusivos com base no valor de COLOR, com prioridade a VERDE se existirem os registros VERDE e VERMELHO para o mesmo produto.

Em suma, se eu tiver o seguinte caso:

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

Gostaria que meu SELECT retornasse as linhas 1, 2 e 4. Como posso conseguir isso?

Minha abordagem atual é ter tabelas separadas e fazer a junção manualmente, mas obviamente esta é uma idéia muito ruim.

Nota: Eu tentei usar a mesma abordagem deAqui:

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");

Mas o resultado que estou obtendo é linhas 1,2,3,4 em vez de apenas 1,2,4. o que estou perdendo?

Editar: Mais uma pergunta, por favor: como isso pode ser feito para trabalhar com um subconjunto de registros, ie. se ao invés da tabela inteira eu quisesse filtrar alguns registros?

Por exemplo, se eu tivesse algo como SELECT * FROM tabela WHERE productID LIKE "1%" ... como posso recuperar cada produto exclusivo, mas ainda respeitando a prioridade de cor (VERDE> VERMELHO)?