Order a query based on a field pointing to the same table
I have a table called "Sentence" which has the following fields:
ID <--- OK
NextID <--- FK To ID
Text
So, if I had the following entries:
*ID* *NextID* *Text*
1 12 The quick
3 40 jumps over
5 null lazy dog.
12 3 brown fox
40 5 the
If I know the start of the sequence is the record with ID = 1, is there a way to order the query based on the NextID sequence. As in the example above, the expected result should be ...
The quick
brown fox
jumps over
the
lazy dog.
I am looking for either a T-SQL / s statement or somehow with Linq. Thanks in advance!
source to share
try this:
declare @YourTable table (RowID int primary key, NextID int, TextValue varchar(50))
INSERT INTO @YourTable VALUES (1 , 12 ,'The quick')
INSERT INTO @YourTable VALUES (3 , 40 ,'jumps over')
INSERT INTO @YourTable VALUES (5 , null,'lazy dog.')
INSERT INTO @YourTable VALUES (12, 3 ,'brown fox')
INSERT INTO @YourTable VALUES (40, 5 ,'the')
;with cteview as (
SELECT * FROM @YourTable WHERE RowID=1
UNION ALL
SELECT y.* FROM @YourTable y
INNER JOIN cteview c ON y.RowID=c.NextID
)
select * from cteview
OPTION (MAXRECURSION 9999) --go beyond default 100 levels of recursion to 9999 levels
OUTPUT:
RowID NextID TextValue
----------- ----------- --------------------------------------------------
1 12 The quick
12 3 brown fox
3 40 jumps over
40 5 the
5 NULL lazy dog.
(5 row(s) affected)
source to share
LINQ response:
table.OrderBy(sentence => sentence.NextID);
Edit: Hopefully this time I answered correctly:
class Sentence
{
public int Id;
public int? NextId;
public string Text;
public Sentence(int id, int? nextId, string text)
{
this.Id = id;
this.NextId = nextId;
this.Text = text;
}
}
var Sentences = new [] {
new Sentence(1, 12, "This quick"),
new Sentence(3, 40, "jumps over"),
new Sentence(5, null, "lazy dog."),
new Sentence(12, 3, "brown fox"),
new Sentence(40, 5, "the"),
};
Func<int?, string> GenerateSentence = null;
GenerateSentence = (id) => id.HasValue? Sentences
.Where(s => s.Id == id.Value)
.Select(s => s.Text + " " + GenerateSentence(s.NextId))
.Single() : string.Empty;
Console.WriteLine(GenerateSentence(1));
source to share
If you are using LINQ to SQL / Entities, then the generated class Sentence
should have all the properties you mentioned, as well as an entity reference to the next clause (call it NextSentence) from a foreign key.
Then you can simply do:
Sentence s = Sentences.First();
StringBuilder sb = new StringBuilder();
do { sb.Append(s.Text); s = s.NextSentence; } while (s != null);
and sb.ToString()
your answer will be.
source to share