title: Recursive SQL To Obtain Original Superseded Row
body: <p>I am using T/SQL in Microsoft SQL Server 2008 and have a user Licence table which has the following structure:</p>
  
  <pre><code>LicenceID (PK, uniqueidentifier, not null)
  SupersededID (FK, uniqueidentifier, not null)
  …other licence related columns
  </code></pre>
  
  <p>When a user upgrades their licence key,  the SupersededID is populated with the original LicenceID. This can occur multiple times and there will therefore always be a trace back to the very first licence that was issued. It is also possible for a licence key to never be superseded.</p>
  
  <p>The difficulty I have is that I need to be able to query all of the rows in the Licences table and extract the very first original licence key for each.</p>
  
  <p>I believe this can be achieved by recursively calling a query using the WITH method, something along these lines, but I'm not totally clear on the concept.. this is what I have so far:</p>
  
  <pre>
  
  
  WITH c
       AS (SELECT SupersededByID,
                  LicenceID,
                  LicenceID AS topParentID
           FROM   Licence
           where SupersededBy IS NOT NULL
           UNION ALL
           SELECT l.SupersededBy,
                  l.LicenceID,
                  c.topparentid
           FROM   Licence AS l
                  INNER JOIN c
                          ON l.id = c.SupersededByID
           WHERE  T.SupersededByID IS NOT NULL)
  
  SELECT *
  FROM   c
  </pre>