X-Received: by 10.182.106.11 with SMTP id gq11mr6587380obb.25.1438755611143; Tue, 04 Aug 2015 23:20:11 -0700 (PDT) X-Received: by 10.182.33.10 with SMTP id n10mr40805obi.18.1438755611073; Tue, 04 Aug 2015 23:20:11 -0700 (PDT) Path: csiph.com!optima2.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!f3no7703857igg.0!news-out.google.com!o13ni7098igw.0!nntp.google.com!f3no7703846igg.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: microsoft.public.sqlserver.programming Date: Tue, 4 Aug 2015 23:20:10 -0700 (PDT) Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=115.248.211.41; posting-account=UdCuKwoAAAB0-uLpIoFcSSp1N3kkQ94p NNTP-Posting-Host: 115.248.211.41 User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Need to fetch data recursively from a table From: siddhant171990@gmail.com Injection-Date: Wed, 05 Aug 2015 06:20:11 +0000 Content-Type: text/plain; charset=ISO-8859-1 Xref: csiph.com microsoft.public.sqlserver.programming:31261 I have a master table like this - Table(Feature, PrjctNo, Instance) Each record here is unique. Say, FTR1, PrjctA, Instance1 FTR1, PrjctB, Instance2 FTR2, PrjctA, Instance2 FTR2, PrjctB, Instance1 FTR3, PrjctA, Instance5 FTR4, PrjctA, Instance0 Basically, each feature can be mapped to multiple projects. And each project can be mapped to multiple feature but the instance of that project for each feature will be unique. What I have is a table with a subset of FTR No's from the master table. Now, I want to retrieve all the projects from the master associated with these features. Now, since each of these projects can be associated to multiple other features, I need to identify those records such that the Instance of the projects is always higher than the already existing one. Ex : Given that I have a subset (FTR1, FTR2) Iteration 1 : FTR1, PrjctA, Instance1 FTR1, PrjctB, Instance2 FTR2, PrjctA, Instance2 FTR2, PrjctB, Instance1 Iteration 2: Check if there any other features associated to any of the projects fetched above. So, after iteration 2, the result set should look like : FTR1, PrjctA, Instance1 FTR1, PrjctB, Instance2 FTR2, PrjctA, Instance2 FTR2, PrjctB, Instance1 FTR3, PrjctA, Instance5 We won't add the record with Instance0 of PrjctA. We always have to fetch the higher instance. How can I accomplish this using SQL server?