title: SQL Challenge - Display N (1, X or All) rows with a certain column value
body: <p>Here's a morning challenge: you have a table with rows like this:</p>
  
  <pre><code>=&gt; select * from candidates;
   id |   name   
  ----+----------
    1 | JOhn Doe
    2 | Melinda
    3 | Bill
    4 | Jane
  (4 rows)
  
  =&gt; select * from evaluation order by id;
   id | score |                reason                
  ----+-------+--------------------------------------
    1 | RED   | Clueless!
    1 | AMBER | Came in dirty jeans
    2 | GREEN | Competenet and experienced
    2 | AMBER | Was chewing a gum
    3 | AMBER | No experience in the industry sector
    3 | AMBER | Has knowledge gaps
  (6 rows)
  </code></pre>
  
  <p>John has a red, Melinda has a green and amber, Bill has just ambers while Jane hasn't been interviewed yet.</p>
  
  <p>Your mission, should you choose to accept it is to generate a query that displays the results for Boss' approval. Boss likes to have results presented as:</p>
  
  <ul>
  <li>If a candidate has a GREEN, then display just  greens and ignore reds and ambers.</li>
  <li>If candidate has reds and ambers or just ambers then display all of them, but have red score appear first so he can skip ambers if RED is really bad.</li>
  <li>display GREY for all candidates that have not been yet interviewed ('Jane')</li>
  </ul>
  
  <p>Rules of the game:</p>
  
  <ul>
  <li>No functions! Must be a single SQL query (however many sub-queries you want)</li>
  <li>Any SQL variant accepted, but ANSI SQL 92 or later gets you more points</li>
  <li>Try to avoid inline variables if you can (@foo in MySQL)</li>
  </ul>
  
  <hr>
  
  <p>My own answer turned out to be in line with group-think:</p>
  
  <pre><code>SELECT *
  FROM   evaluation e1
         NATURAL JOIN candidates
  WHERE  score = 'GREEN'
          OR ( score IN ( 'RED', 'AMBER' )
               AND NOT EXISTS (SELECT 1
                               FROM   evaluation e2
                               WHERE  e1.id = e2.id
                                      AND score = 'GREEN') )
  UNION
  SELECT id,
         'GREY'              AS score,
         'Not yet evaluated' AS reason,
         name
  FROM   candidates
  WHERE  id NOT IN (SELECT id
                    FROM   evaluation)
  ORDER  BY 1,
            2 DESC  
  </code></pre>