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

SELECT *
FROM yourtable
WHERE ProductID = (your id)
ORDER BY colour
LIMIT 1

select yourtable.*
from
    yourtable
    inner join
    (select productid, min(colour) mincolour
    from yourtable
    where productid like '10%'
    group by productid) v
    on yourtable.productid=v.productid
    and yourtable.colour=v.mincolour

SELECT top 1 *
FROM <table>
WHERE ProductID = <id>
ORDER BY case when colour ='GREEN' then 1
              when colour ='RED' then 2 end