There is a table of the following type, sorted by the Par_Id , Chi_id :

  Par_id Chi_id 0 1 0 2 0 3 1 4 1 5 2 6

And it is necessary:

Sort by Par_id , with the Par_id value corresponding to some Chi_id (maybe several), we find this Chi_id in the Par_id field and sort it. Then the procedure is repeated until we run through all the elements.

Should take the following form:

  Par_id Chi_id 0 1 1 4 1 5 0 2 2 6 0 3

Thanks to all!

  • "under Chi_id condition" - under what condition? - kmv
  • @kmv corrected the condition. - romka.pm

3 answers 3

SELECT * FROM table1 ORDER BY Chi_id,Par_id like this!

  • describe in more detail what you want to receive. You have a table that looks like it is on the first piece. Do you want on the second? - HELO WORD
  • Yes that's right. Few corrected. - romka.pm
  • select * from table_name t1 start with t.id =? connect by prior t.par_id = t.id; on MySQL, I don’t know how to spell it, but on oracle I wrote like this - HELO WORD

If you need to display a tree by arranging the nodes according to their location in the tree, this is done by a recursive query:

 ;with tree as ( select Par_id, Chi_id, Sort_Key = cast(Chi_id as varbinary(400)) from [TableName] where Par_id = 0 union all select t.Par_id, t.Chi_id, cast(tree.Sort_Key + cast(t.Chi_id as varbinary(8)) as varbinary(400)) from tree join [TableName] t on t.Par_id = tree.Chi_id ) select Par_id, Chi_id from tree order by Sort_Key;

    The idea suggested in the adjacent answer is correct, only the key for sorting should be made up a little differently (in Transact-SQL syntax):

     with tree as ( select Par_id, Chi_id, cast(Par_id as varchar(max)) + cast(Chi_id as varchar(max)) as k from TableName where Par_id = 0 union all select t.Par_id, t.Chi_id, tree.k + cast(t.Par_id as varchar(max)) + cast(t.Chi_id as varchar(max)) from tree join TableName t on t.Par_id = tree.Chi_id ) select Par_id, Chi_id from tree order by k
    • @kvm It will not be difficult for you to help using this recursion for my query. Request - romka.pm
    • @ romka-pm do you need to output data from the top query in this form? You can upload it to a temporary table in the same with and then use it in a recursive query: pastebin.com/pH9gFSLr . - kmv
    • @kvm view is not important. Swears: "incorrect syntax near the with construction" (2 constructions) - romka.pm
    • @ romka.pm yes, there is an error. The word with before a tree must be removed. - kmv
    • Such a key is not entirely correct. If two entries (3, 7) and (3, 10) added to the initial data in the start-post, then when sorting, a dozen will go ahead of the seven, and not after. - i-one