0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for cartographers, geographers and GIS professionals

WITH t
as (
SELECT 1 as id, 'Dan' as Name, 'N' as registered
UNION SELECT 2,   'Bill'   ,  'N'
UNION SELECT 3,   'Bob'   ,   'N'
UNION SELECT 4,   'Dan'  ,    'N'
UNION SELECT 5,   'Bill',     'Y'
UNION SELECT 6,   'Dan',      'Y')
, 
 ns 
     AS (SELECT Row_number() OVER (PARTITION BY name ORDER BY id) rn, 
                id, 
                name, 
                registered 
         FROM   t 
         WHERE  registered = 'N') 
SELECT t.id, 
       t.name, 
       ns.id, 
       ns.name 
FROM   ns 
       LEFT JOIN t 
         ON ns.name = t.name 
            AND t.registered = 'Y' 
            AND ns.rn = 1 
WHERE  ns.name IN (SELECT name 
                   FROM   t 
                   WHERE  registered = 'Y')  

ORDER  BY ns.name

fork query

edited feb 16 12

created feb 16 12 conradfrix