0

This is a continuation of my previous question here.

In the following example:

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

Given a product ID, I want to retrieve only one record - that with GREEN colour, if one exists, or the RED one otherwise. It sounds like I need to employ DISTINCT somehow, but I don't know how to supply the priority rule.

Pretty basic I'm sure, but my SQL skills are more than rusty..

Edit: Thank you everybody. One more question please: how can this be made to work with multiple records, ie. if the WHERE clause returns more than just one record? The LIMIT 1 would limit across the entire set, while what I'd want would be to limit just within each product.

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)?

Community
  • 1
  • 1
lucianf
  • 547
  • 7
  • 17

2 Answers2

2

try this:

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

If you want to order it based on another color, you can give it in the case statement

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
  • Thanks, the case makes it more generic than the other answer, but the top 1 doesn't work in sqlite. – lucianf Aug 11 '12 at 15:06
1
SELECT * 
FROM yourtable
WHERE ProductID = (your id)
ORDER BY colour 
LIMIT 1

(Green will come before Red, you see. The LIMIT clause returns only one record)

For your subsequent edit, you can do this

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
podiluska
  • 50,950
  • 7
  • 98
  • 104