CODEWITHSUNDEEP
sql-servert-sqlsorting
Lennart Stromberg
Lennart Stromberg

Reputation: 43

Sorting SQL table

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

Upvotes: 1

Views: 2189

Answers (5)

KM.
KM.

Reputation: 103697

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)

Upvotes: 4

Andy Clarke
Andy Clarke

Reputation: 3262

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

Upvotes: 0

Jay S
Jay S

Reputation: 7994

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.

Upvotes: 0

curtisk
curtisk

Reputation: 20175

SELECT     ID, Comment, ParentId
FROM         TestTable
ORDER BY Comment, ParentId asc

Upvotes: 0

chub
chub

Reputation: 787

humm order by?

http://t-sql.pro/t-sql/ORDER-BY.aspx

Upvotes: 1

Related Questions