First of all, the title of this question is horrible, but I didn’t find a better way to describe my issue.

There’s probably a very easy way to do this, but I couldn’t figure it out. This is very similar to this question, but I’m running on sqlite3 (iOS) so I suspect my options are much more limited.

I have a table with product records. All records have an ID (note: I’m not talking about the row ID, but rather an identification number unique to each product). Some products may have two entries in the table (both with the same ID). The only difference would be in a special column (let’s say column COLOUR can be either RED or GREEN).

What I want to do is create a list of unique products based on the value of COLOUR, with priority to GREEN if both GREEN and RED records exist for the same product.

In short, if I have the following case:

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

I would like my SELECT to return the rows 1, 2 and 4. How can I achieve this?

My current approach is to have separate tables, and do the join manually, but obviously this is a very bad idea..

Note: I’ve tried to use the same approach from here:

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

But the result I’m getting is rows 1,2,3,4 instead of just 1,2,4. What am I missing?

Edit: One more question please: how can this be made to work with a subset of records, ie. if instead of the entire table I wanted to filter some records?

For example, if I had something like SELECT * FROM table WHERE productID LIKE “1%” … how can I retrieve each unique product, but still respecting the colour priority (GREEN>RED)?

Your query is nearly correct. Just use PRODUCTID and not ID.

SELECT * 
FROM xx
WHERE f_COLOUR = "GREEN"
UNION
SELECT * 
FROM xx 
WHERE PRODUCTID not in 
                (SELECT PRODUCTID
                 FROM xx 
                 WHERE f_COLOUR = "GREEN");

SQLFiddle Demo

Try this

SELECT * 
FROM xx
WHERE COLOUR = 'GREEN'
UNION
SELECT * 
FROM xx WHERE P_Id not in 
                (SELECT P_Id
                 FROM Persons 
                 WHERE COLOUR = 'GREEN');

See ALSO SQL FIDDLE DEMO

I just want to offer that you can do this with a group by:

select (case when sum(case when colour = 'Green' then 1 else 0 end) > 0
             then max(case when colour = 'Green' then id end)
             else max(case when colour = 'Red' then id end)
        end) as id,
       product_id
       (case when sum(case when colour = 'Green' then 1 else 0 end) > 0 then 'Green'
             else 'Red'
        end) as colour
from t
group by product_id

You can have it like this

WITH PriorityTable
AS
(
    SELECT T.*,
        ROW_NUMBER() OVER (PARTITION BY T.ID
                            ORDER BY PT.ColorPriority )  PriorityColumn
    FROM XX AS T
    INNER JOIN (
        SELECT 'RED' AS f_COLOUR , 1 AS ColorPriority
        UNION
        SELECT 'GREEN' AS f_COLOUR , 2 AS ColorPriority
    ) AS PT
        ON T.f_COLOUR  = PT.f_COLOUR 
)

SELECT * FROM PriorityTable
WHERE PriorityColumn = 1