Groups > microsoft.public.sqlserver.programming > #31264

Re: Need to fetch data recursively from a table

X-Received	by 10.140.150.88 with SMTP id 85mr7367132qhw.11.1438767508781; Wed, 05 Aug 2015 02:38:28 -0700 (PDT)
X-Received	by 10.182.33.10 with SMTP id n10mr43026obi.18.1438767508693; Wed, 05 Aug 2015 02:38:28 -0700 (PDT)
Path	csiph.com!au2pb.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!z61no5349041qge.0!news-out.google.com!o13ni7107igw.0!nntp.google.com!f3no7774735igg.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	microsoft.public.sqlserver.programming
Date	Wed, 5 Aug 2015 02:38:28 -0700 (PDT)
In-Reply-To	<XnsA4ED6A4C86392Yazorman@127.0.0.1>
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
References	<ee908b1e-167e-466e-9c16-9d1482d15e6e@googlegroups.com> <XnsA4ED6A4C86392Yazorman@127.0.0.1>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<00b870d8-13dc-49d0-814f-1c552fe045df@googlegroups.com> (permalink)
Subject	Re: Need to fetch data recursively from a table
From	siddhant171990@gmail.com
Injection-Date	Wed, 05 Aug 2015 09:38:28 +0000
Content-Type	text/plain; charset=ISO-8859-1
Lines	54
Xref	csiph.com microsoft.public.sqlserver.programming:31264

Show key headers only | View raw

On Wednesday, 5 August 2015 13:57:01 UTC+5:30, Erland Sommarskog  wrote:
> (siddhant171990@gmail.com) writes:
> > 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. 
> > 
> 
> There are a few things that are unclear to. For instance, what if we find a 
> feature which is related to a project not related to the original set?
> 
> Below is a script where I have augmented your test data with a few more 
> data points, but I don't know if the result is the expected with regards 
> to this data.
> 
> CREATE TABLE  tbl(feature  char(4) NOT NULL,
>                   project  char(1) NOT NULL,
>                   instance int     NOT NULL,
>                   PRIMARY KEY (feature, project, instance))
> 
> go
> INSERT tbl(feature, project, instance)
>   VALUES ('FTR1', 'A', 1),
>          ('FTR1', 'B', 2),
>          ('FTR2', 'A', 2),
>          ('FTR2', 'B', 1),
>          ('FTR3', 'A', 5),
>          ('FTR3', 'C', 5),
>          ('FTR4', 'A', 0),
>          ('FTR4', 'B', 9)
> go
> ; WITH r AS (
>     SELECT feature, project, instance
>     FROM   tbl
>     WHERE  feature IN ('FTR1', 'FTR2')
>     UNION ALL
>     SELECT tbl.feature, tbl.project, tbl.instance
>     FROM   tbl
>     JOIN   r ON tbl.project = r.project
>             AND tbl.instance > r.instance
> )
> SELECT DISTINCT feature, project, instance
> FROM   r
> go
> DROP TABLE tbl
> 
> 
> 
> -- 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

It doesn't work. I get the same record multiple times.

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Find similar

Thread

Need to fetch data recursively from a table siddhant171990@gmail.com - 2015-08-04 23:20 -0700
  Re: Need to fetch data recursively from a table Erland Sommarskog <esquel@sommarskog.se> - 2015-08-05 10:26 +0200
    Re: Need to fetch data recursively from a table siddhant171990@gmail.com - 2015-08-05 01:57 -0700
      Re: Need to fetch data recursively from a table Erland Sommarskog <esquel@sommarskog.se> - 2015-08-05 13:03 +0200
        Re: Need to fetch data recursively from a table siddhant agarwal <siddhant171990@gmail.com> - 2015-08-05 04:06 -0700
    Re: Need to fetch data recursively from a table siddhant171990@gmail.com - 2015-08-05 02:38 -0700

csiph-web