Trying to sort Merchants under hierarchies

Viewed 19

I am running the following code in Postgresql

SELECT merchant_number, immediate_partner, hierarchy_level FROM t.merchant_data ORDER BY hierarchy_level

and get the following results:

merchant_number immediate_partner hierarchy_level
3012 NULL 10
:--------------- :----------------: ---------------:
3113 3012 9
:--------------- :----------------: ---------------:
3214 3012 9
:--------------- :----------------: ---------------:
3315 3113 7
:--------------- :----------------: ---------------:
3416 3113 6
:--------------- :----------------: ---------------:
3517 3315 4
:--------------- :----------------: ---------------:

The immediate_partner column shows which merchant is immediately above the current merchant in hierarchy.

What I need to do is get all the data in order such that lower level merchants are directly under their immediate partner, starting from the lower level.

The result I am aiming for is the below:

merchant_number immediate_partner hierarchy_level
3012 NULL 10
:--------------- :----------------: ---------------:
3113 3012 9
:--------------- :----------------: ---------------:
3315 3113 7
:--------------- :----------------: ---------------:
3517 3315 4
:--------------- :----------------: ---------------:
3416 3113 6
:--------------- :----------------: ---------------:
3214 3012 9
:--------------- :----------------: ---------------:

3012 has a NULL immediate partner as that is the top of the chain as you can see 3113 which falls under 3012 is directly below it, and all other merchants that fall below that are directly after it, before the next hierarchy 9 merchant (in this case 3214 which also falls under 3012 appears in the list).

This enables me to see which merchants fall directly under which hierarchy_level 9 merchant for example by just checking the list until I find the next hierarchy_level 9.

Would you be able to please help me with the postgresql code I will need? I would really appreciate your help! thanks!

0 Answers
Related