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 
       LEFT JOIN t 
         ON ns.name = t.name 
            AND t.registered = 'Y' 
            AND maxns.id IS NOT NULL 
WHERE  ns.registered = 'N' 
       AND ns.name IN (SELECT name 
                       FROM   t 
                       WHERE  registered = 'Y') 
ORDER  BY ns.name, 
          t.name

Enter Parameters

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