SQL Challenge - Display N (1, X or All) rows with a certain column value

Here's a morning challenge: you have a table with rows like this:

=> select * from candidates;
 id |   name   
----+----------
  1 | JOhn Doe
  2 | Melinda
  3 | Bill
  4 | Jane
(4 rows)

=> select * from evaluation o开发者_JS百科rder 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)

John has a red, Melinda has a green and amber, Bill has just ambers while Jane hasn't been interviewed yet.

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:

• If a candidate has a GREEN, then display just greens and ignore reds and ambers.
• 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.
• display GREY for all candidates that have not been yet interviewed ('Jane')

Rules of the game:

• No functions! Must be a single SQL query (however many sub-queries you want)
• Any SQL variant accepted, but ANSI SQL 92 or later gets you more points
• Try to avoid inline variables if you can (@foo in MySQL)

My own answer turned out to be in line with group-think:

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  

The following is transitional SQL-92:

SELECT c.name, e.*
  FROM candidates AS c
       JOIN (
             SELECT *
               FROM evaluation
              WHERE score = 'GREEN'
             UNION
             SELECT *
               FROM evaluation AS e1
              WHERE score IN ('AMBER', 'RED')
                    AND NOT EXISTS (
                                    SELECT * 
                                      FROM evaluation AS e2
                                     WHERE e2.id = e1.id
                                           AND e2.score = 'GREEN'
                                   )                                    
            ) AS e 
          ON c.id = e.id
UNION
SELECT c.name, c.id, 'GREY', '(not interviewed)'
  FROM candidates AS c
 WHERE NOT EXISTS (
                   SELECT *
                     FROM evaluation AS e 
                    WHERE e.id = c.id
                   )
ORDER BY id, score DESC;

Alternate (Intermediate SQL-92):

SELECT c.name, e.id, e.score, e.reason 
  FROM candidates AS c
       JOIN (
             SELECT *
               FROM evaluation
             EXCEPT
             SELECT *
               FROM evaluation
              WHERE score IN ('AMBER', 'RED')
                    AND id IN ( SELECT id FROM evaluation WHERE score = 'GREEN' )
            ) AS e 
          ON c.id = e.id
UNION
SELECT name, id, 'GREY' AS score, '(not interviewed)' AS reason
  FROM candidates
 WHERE id NOT IN ( SELECT id FROM evaluation )
ORDER BY id, score DESC;

SELECT
        c.id                       AS id
      , c.name                     AS name
      , COALESCE(e.score, 'GREY')  AS score
      , e.reason                   AS reason
FROM 
        candidates    c
    LEFT JOIN
        evaluation    e
            ON e.id = c.id
WHERE
        e.score = 'GREEN'
    OR
        NOT EXISTS
          ( SELECT *
            FROM evaluation    ee
            WHERE ee.id = c.id
              AND ee.score = 'GREEN'
          )
ORDER BY
        id      ASC
      , score   DESC 

           SELECT c.id,
               c.name,
               nvl(e.score, 'GREY'),
               nvl(e.reason, 'Not yet interviewed')
          FROM candidates c, evaluation e
         where c.id = e.id(+)
           and ((e.score = 'GREEN') or
               (e.score in ('RED', 'AMBER') and not exists
                (select null
                    from evaluation e2
                   where e2.id = e.id
                     and e2.score = 'GREEN')) or e.score is null)
         order by c.id, e.score desc;

SELECT A.ID
    , A.NAME
    , B.SCORE
    , B.REASON
FROM CANDIDATES A
LEFT JOIN EVALUATION B
    ON A.ID = B.ID
WHERE B.SCORE = 'GREEN'
UNION
SELECT A.ID
    , A.NAME
    , COALESCE(B.SCORE,'GREY')
    , B.REASON
FROM CANDIDATES A
LEFT JOIN EVALUATION B
    ON A.ID = B.ID
WHERE A.ID NOT IN (SELECT ID FROM EVALUATION WHERE SCORE = 'GREEN')
ORDER BY ID, SCORE DESC;