In my database I have a Tracking Codes table as a child of a single Employment table. Along with other data, I am trying to return all of the Tracking Codes in a comma separated string so there will only be one row per Employment. However, I want this string sorted by the Tracking Code Order table that will give a numerical order depending on the Tracking Code.

My Tracking Code Tables look like this:

TrackingCodeOrder Table
| Name | Order |
| ------------ | ------------ |
| A | 1 |
| B | 2 |
| C | 3 |

TrackingCode Table

The current code I am using is:

(
    select top(1) string_agg(tc.TrackingCode, ',') 
    from TrackingCode as tc 
    left join TrackingCodeOrder tco on tco.Name = tc.TrackingCode
    where etc.Employment_id = e.id
    group by tco.Order
    order by tco.Order asc
) as 'TrackingCodeId'

from employee e

When I attempt to do it with this code, it runs, but I do not get all of my tracking codes returned. Some have 2 but will only return 1, others will have 9 but only return 4. If I remove the top(1), group by, and order by, I will see all the codes returned correctly, but not in the expected order. I have also attempted using top 100 percent . But the sub select will return more than one item.

Is there another way that I can sort in this sub select?