Sample for 9320149

0

Please login or register to vote for this query.

(click on this box to dismiss)

Windows Phone Meta

Q&A about the site for enthusiasts and power users of Windows Phone OS

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')

 SELECT t.id, 
           t.name, 
           ns.id, 
           ns.name 
           FROM   
           t ns
           LEFT JOIN 
           (SELECT 
      MAX(ID) id, 
      Name
FROM t
WHERE 
     registered = 'N'
     GROUP BY Name) maxns
     ON ns.id = maxns.id 
     and ns.registered = 'N'
           LEFT JOIN t 
             ON ns.name = t.name 
                AND t.registered = 'Y' 
                AND maxns.id is not null
    WHERE  ns.name IN (SELECT name 
                       FROM   t 
                       WHERE  registered = 'Y')  

    ORDER  BY ns.name

Enter Parameters

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