2 Replies Latest reply: Jul 3, 2013 10:43 PM by Khim Hoe Tan

# How to Find Highest level in hierarchy

Hi all,

I have a table that contain a list of accounts in a hierarchical format. (for example, level 1 to 5) The account table looks like this:-

Level 1
Level 2Level 3Level 4Level 5
1110011100111001100010000
1120011200112001100010000
1130011300113001100010000
1210012100121001200010000
1220012200122001200010000

Level 1 is the lowest level and Level 5 is the highest. Level 1 is linked to the fact table. Now, my question is if I'm given an account number without knowing the level, how can I find the accounts of the lowest level?

For example (Based on the above sample):

If account 11000 is given, the accounts that should be returned are 11100, 11200, 11300.

If account 10000 is given, the accounts that should be returned are 11100, 11200, 11300, 12100, 12200.

If account 12100 is given, the accounts that should be returned are 12100.

Some note:-

- account number is unique

- there is not pattern in the account number. All account numbers are randomly created.

• ###### Re: How to Find Highest level in hierarchy

See attached example.

• ###### Re: How to Find Highest level in hierarchy

Thanks! Works perfectly.