I have this data:
| parent_id | comment_id | comment_level |
|---|---|---|
| NULL | 0xB2 | 1 |
| NULL | 0xB6 | 1 |
| NULL | 0xBA | 1 |
| NULL | 0xBE | 1 |
| NULL | 0xC110 | 1 |
| NULL | 0xC130 | 1 |
| 123 | 0xC13580 | 2 |
| 456 | 0xC135AC | 3 |
| 123 | 0xC13680 | 2 |
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.
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_level. If the comment_level is already 1, the row is its own parent. Then we can sort first by the comment_id from that parent record DESC, and then sort ascending by the local comment_id within the a given group of matching parent records.
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:
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: