SQL Query for sorting in particular order

Question

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 really don't follow what you are trying to say in the first sentence. I suggest breaking it down more. — Thom A
– Thom A ♦, Commented Dec 15, 2022 at 17:03
Yes, I know it doesn't look good. Can you see the output I'm expecting? — Joe Goldberg
– Joe Goldberg, Commented Dec 15, 2022 at 17:34
You need to use spaces on this site for the table formatting. The tabs will render inconsistently depending on what device and browser users have. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 15, 2022 at 17:41

Joel Coehoorn · Accepted Answer · 2023-01-06 23:05:40Z

0

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:

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

edited Jan 6, 2023 at 23:05

answered Dec 15, 2022 at 17:45

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Goldberg Over a year ago

Yes exactly I want to do that and using this it is giving partially correct all comment_level >1 are ascending order but not below their parent.

Joel Coehoorn Over a year ago

@AditMehta Putting them below their parent is what the case expression in the order by clause is for. Maybe adjust that to get this working.

Joel Coehoorn Over a year ago

Otherwise, if you can add a few more rows to the sample data and results to show where it's going wrong, that would help, because the fiddle provides the exact same output as was expected in the question.

Ben Thul · Accepted Answer · 2022-12-15 20:45:44Z

Those varbinary values aren't arbitrary, they're hierarchyid values. And just a guess, they're probably typed that way in the schema (it's too much to be a coincidence). We can use this fact to do what we're looking to do.

with d_raw as (
    select * from (values
        (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)
    ) as x(parent_id, comment_id, comment_level)
),
d as (
    select parent_id, comment_id = cast(comment_id as hierarchyid), comment_level
    from d_raw
)
select *,
    comment_id.ToString(), 
    comment_id.GetAncestor(comment_id.GetLevel() - 1).ToString()
from d
order by comment_id.GetAncestor(comment_id.GetLevel() - 1) desc, 
    comment_id

Note - the CTEs that I'm using are just to get the data into the right format and the last SELECT adds additional columns just to show what's going on; you can omit them from your query without consequence. I think the only interesting part of this solution is using comment_id.GetAncestor(comment_id.GetLevel() - 1) to get the root-level node.

theking2 · Accepted Answer · 2022-12-16 18:24:56Z

0

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)

edited Dec 16, 2022 at 18:24

answered Dec 15, 2022 at 17:23

theking2

3,1002 gold badges36 silver badges51 bronze badges

2 Comments

Joe Goldberg Over a year ago

I have already tried this I can't use 2 order by in one union

theking2 Over a year ago

try it including parentheces

Collectives™ on Stack Overflow

SQL Query for sorting in particular order

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related