can anyone help me with T-SQL to sort this table
ID Comment ParentId
-- ------- --------
3 t1 NULL
4 t2 NULL
5 t1_1 3
6 t2_1 4
7 t1_1_1 5
to look like this
ID Comment ParentId
-- ------- --------
3 t1 NULL
5 t1_1 3
7 t1_1_1 5
4 t2 NULL
6 t2_1 4
Kind regards,
Lennart
try this:
DECLARE @YourTable table (id int, Comment varchar(10), parentID int)
INSERT INTO @YourTable VALUES (3, 't1' , NULL)
INSERT INTO @YourTable VALUES (4, 't2' , NULL)
INSERT INTO @YourTable VALUES (5, 't1_1' , 3)
INSERT INTO @YourTable VALUES (6, 't2_1' , 4)
INSERT INTO @YourTable VALUES (7, 't1_1_1', 5)
;with c as
(
SELECT id, comment, parentid, CONVERT(varchar(8000),RIGHT('0000000000'+CONVERT(varchar(10),id),10)) as SortBy
from @YourTable
where parentID IS NULL
UNION ALL
SELECT y.id, y.comment, y.parentid, LEFT(c.SortBy+CONVERT(varchar(8000),RIGHT('0000000000'+CONVERT(varchar(10),y.id),10)),8000) AS SortBy
FROM c
INNER JOIN @YourTable y ON c.ID=y.PArentID
)
select * from C ORDER BY SortBy
EDIT
here is output
id comment parentid SortBy
----------- ---------- ----------- ---------------------------------
3 t1 NULL 0000000003
5 t1_1 3 00000000030000000005
7 t1_1_1 5 000000000300000000050000000007
4 t2 NULL 0000000004
6 t2_1 4 00000000040000000006
(5 row(s) affected)
humm order by?
SELECT ID, Comment, ParentId
FROM TestTable
ORDER BY Comment, ParentId asc
This sounds very much like a homework question, but here's some hints on where to go with this:
You'll want to do a quick google or StackOverflow search for the ORDER BY clause to be able to get a set of results ordered by the column you want to use (i.e. the 'Comment' column). Once you've got that, you can start writing a SQL statement to order your results.
If you need to then place re-order the actual table (and not just get the results in a specific order), you'll need to look up using temporary tables (try searching for 'DECLARE TABLE'). Much like any temp swap, you can place the results you have in a temporary place, delete the old data, and then replace the table contents with the temporary data you have, but this time in the order you want.
But just ordering by Comment will give you that? Or have I missed the point?!
declare @table table
(
Comment varchar(10)
)
insert into @table (Comment) values ('t1')
insert into @table (Comment) values ('t2')
insert into @table (Comment) values ('t1_1')
insert into @table (Comment) values ('t2_1')
insert into @table (Comment) values ('t1_1_1')
select * from @table order by comment
