i convrted your table data into a CTE so we had code to test with.

I was thinking using a dense rank would get you your one row)number per group, but...

i slapped together an example, but the logic is this:

join the tables together and use a group by. getting the max() of each column so you get the desired look...

but wouldn't licensing costs be a major cost issue, if the services are spread out on multiple servers?
I believe software assurance would let us have an active /...

very helpful thank you!
so for my lab example then, where i eventually have all services on each node of the cluster, i would install SSAS/SSIS as clustered services,...