0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people interested in improving and participating in the patent system

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 , t2.*
FROM   ns 
LEFT JOIN t 
ON ns.name = t.name
and t.Registered = 'Y'
and ns.rn = 1
left join t t2  
ON ns.name = t2.name
and t.Registered = 'Y'

ORDER BY ns.name

view current version

edited feb 16 12

created feb 16 12 conradfrix