Sample for 9320149

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mythology

Q&A for enthusiasts and scholars of mythology and folklore

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
ORDER BY ns.name

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results