If you deal with a table that has foreign key to itself, circular references may appear. The query that lists all rows with circular references is listed at the page.

Sometimes it is necessary to list only representatives of each set of circular referenced rows. In the following query the first select statement orders representatives by its IDs; the second one – by the latest create time, listed in the another table.

************************ Representatives.sql *************************
USE [Example]
GO

— create tables
CREATE TABLE [dbo].[List]
    ([ID] [int] IDENTITY(1,1) NOT NULL
    ,[Name] [varchar](50) NULL
    ,[ParentID] [int] NULL
    ,CONSTRAINT [PK_List] PRIMARY KEY CLUSTERED ([ID] ASC));
GO

CREATE TABLE [dbo].[ListTime]
    ([ID] [int] IDENTITY(1,1) NOT NULL
    ,[ListID] [int] NOT NULL
    ,[CurrentDate] [datetime] NOT NULL
    ,CONSTRAINT [PK_ListTime] PRIMARY KEY CLUSTERED ([ID] ASC));
GO

ALTER TABLE [dbo].[ListTime]
    ADD CONSTRAINT [DF_ListTime_CurrentDate] DEFAULT (SYSDATETIME()) FOR [CurrentDate];
ALTER TABLE [dbo].[ListTime] WITH CHECK
    ADD CONSTRAINT [FK_ListTime_List] FOREIGN KEY([ListID]) REFERENCES [dbo].[List] ([ID]);
GO

— insert values
INSERT INTO [dbo].[List]
VALUES
     ('text 1', 3)
    ,('text 2', NULL)
    ,('text 3', 1)
    ,('text 4', 7)
    ,('text 5', 4)
    ,('text 6', 5)
    ,('text 7', 6)
    ,('text 8', 9)
    ,('text 9', 8)
    ,('text 10', 11)
    ,('text 11', 13)
    ,('text 12', 13)
    ,('text 13', 13)

INSERT INTO [dbo].[ListTime]
VALUES
     (1, '2013-12-01')
    ,(2, '2013-12-02')
    ,(3, '2013-12-03')
    ,(4, '2013-12-04')
    ,(5, '2013-12-05')
    ,(6, '2013-12-06')
    ,(7, '2013-12-07')
GO

— temporary table for all pairs
DECLARE @Pairs TABLE
    ([ID] int
    ,[ParentID] int);

WITH Pairs ([ID], [ParentID]) AS
(
    SELECT    [ID], [ParentID]
    FROM    [List]
    WHERE    [ParentID] IS NOT NULL
    UNION ALL
    SELECT    t1.[ID], t2.[ParentID]
    FROM    List t1
            INNER JOIN Pairs t2 ON t1.[ParentID] = t2.[ID] AND t1.[ParentID] <> t1.[ID] AND t2.[ParentID] <> t2.[ID]
)
INSERT INTO @Pairs
SELECT *
FROM Pairs

— test all ordered pairs
SELECT *
FROM @Pairs
— uncomment in order to see all circular references
–WHERE [id] = [parentid]
ORDER BY [id]

— select representatives from each loop by minimum id
SELECT     DISTINCT MIN(t1.[ParentID])
FROM     @Pairs AS t1
WHERE     t1.[ID] IN (SELECT s1.[ID] FROM @Pairs AS s1 WHERE s1.[ID] = s1.[ParentID])
GROUP BY t1.[ID]

— select representatives from each loop by maximum time from referenced table
SELECT DISTINCT
        (SELECT TOP(1) s1.[ParentID]
         FROM     @Pairs AS s1
                 LEFT OUTER JOIN dbo.[ListTime] AS s2 ON s1.[ParentID] = s2.[ListID]
        WHERE     s1.id = t1.id
        ORDER BY ISNULL(s2.[CurrentDate], DATEADD(MS, s1.[ParentID], '1900-01-01')) DESC) AS [Origin]
FROM    @Pairs AS t1
WHERE    t1.[ID] = t1.[ParentID]

************************ End of Representatives.sql, v.2 *************************

1. All used IP-addresses, names of servers, workstations, domains, are fictional and are used exclusively as a demonstration only.
2. Information is provided «AS IS».