Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Need to fetch data recursively from a table

Show all headers | 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