Getting record with the only document by priority issue

I have two tables:

contacts:

id, name
1   Alex
2   John

documents:

id, contactID, type
1     1         1
2     1         2
...
30    1  开发者_开发问答       3
31    2         1
32    2         3

I want to get contact name, and document type. the only record for each contact. Contact may have several document types (the only document per type), and I have the following priority for document types: 2, 3, 1. For example, Alex has documents of all types, but I should get only:

Alex, 2

John has document types = 1, 3, as a result I should get:

John, 3

Oracle database. But if you'll be able to give sql standard solution, it would be great

Oracle 9i+, use:

WITH example AS (
  SELECT c.name,
         d.type,
         ROW_NUMBER() OVER (PARTITION BY c.id
                                ORDER BY CASE d.type
                                           WHEN 2 THEN 1
                                           WHEN 3 THEN 2
                                           WHEN 1 THEN 3
                                           ELSE 4
                                         END) AS rnk    
    FROM CONTACTS c
    JOIN DOCUMENTS d ON d.contactid = c.id)
SELECT e.name, e.type
  FROM example e
 WHERE e.rnk = 1

...or the non-Subquery Factoring (AKA CTE) version (still 9i+):

SELECT e.name, e.type
  FROM (SELECT c.name,
               d.type,
               ROW_NUMBER() OVER (PARTITION BY c.id
                                      ORDER BY CASE d.type
                                                 WHEN 2 THEN 1
                                                 WHEN 3 THEN 2
                                                 WHEN 1 THEN 3
                                                 ELSE 4
                                               END) AS rnk    
          FROM CONTACTS c
          JOIN DOCUMENTS d ON d.contactid = c.id) e
 WHERE e.rnk = 1

Use an inline view where you map types to priorities, then group by contact, then map priorities back to types.

SELECT C.name
       DECODE( V.priority, 'A', 2, 'B', 3, 'C', 1 ) AS type
FROM   contacts C
    ,  (    SELECT   D.contactid
                ,    MIN( DECODE( D.type, 2, 'A', 3, 'B', 1, 'C' ) ) AS priority
            FROM     documents D
            GROUP BY D.contact_id
       ) V
WHERE  V.contactid = C.id