Selecione registros com base na prioridade da coluna
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)?