I have this data:

I want the result in such a way that rows where comment_level=1 should be in descending order by comment_id and other rows(i.e. where comment_level!=1) should be in ascending order by comment_id but the order of comment level greater than 1 should be inserted according to to order of comment level 1 (what I mean is that rows with comment_level=1 should remain in descending order and then rows with comment_level!=1 should be inserted in increasing order but it should be inserted following rows where comment_id is less than it)

Result should look like this

NULL    0xC130     1
123     0xC13580   2
456     0xC135AC   3
123     0xC13680   2
NULL    0xC110     1
NULL    0xBE       1
NULL    0xBA       1
NULL    0xB6       1
NULL    0xB2       1

Note the bold rows in above sort by comment_id in ascending order, but they come after their "main" row (with comment_level = 1), where these main rows sort DESC by comment_id.

I tried creating 2 tables for different comment level and used sorting for union but it didn't work out because 2 different order by doesn't work maybe I tried from this Using different order by with union but it gave me an error and after all even if this worked it still might not have given me the whole answer.

CodePudding user response：

One way to do this and possibly the only one is create two separate queries and union them together. Something like:

select * from table where comment_level = 1 order by comment_id desc
union
select * from table where not comment_level = 1 order by comment_id asc

CodePudding user response：

I think I understand what you're going for, and a UNION will not be able to do it.

To accomplish this, each row needs to match with a specific "parent" row that does have 1 for the comment_id. (If the comment_id is already 1, the row is its own parent). Then we can sort first by that parent field DESC, and within the a given group of matching parent fields sort ascending.

You'll need something like this:

SELECT t0.*
FROM [MyTable] t0
CROSS APPLY (
    SELECT TOP 1 comment_id 
    FROM [MyTable] t1 
    WHERE t1.comment_level = 1 AND t1.comment_id <= t0.comment_id 
    ORDER BY t1.comment_id DESC
) parents
ORDER BY parents.comment_id DESC, 
    case when t0.comment_level = 1 then 0 else 1 end, 
    t0.comment_id

See it work here:

https://dbfiddle.uk/qZBb3YjO

There's probably also a solution using a windowing function that will be more efficient.

And here it is:

SELECT parent_id, comment_id, comment_level 
FROM (
    SELECT t0.*, t1.comment_id as t1_comment_id
        , row_number() OVER (PARTITION BY t0.comment_id ORDER BY t1.comment_id desc) rn 
    FROM [MyTable] t0
    LEFT JOIN [MyTable] t1 ON t1.comment_level = 1 and t1.comment_id <= t0.comment_id
) d
WHERE rn = 1
ORDER BY t1_comment_id DESC,
  case when comment_level = 1 then 0 else 1 end,
  comment_id

See it here:

https://dbfiddle.uk/me1vGNdM