How to order by nulls-last by a linked table

Question

I have two tables Person and PersonSkill

Person

ID	NAME
1	Person 1
2	Person 2
3	Person 3

PersonSkill

PERSON_ID	SKILL	SORT
1	Sing	20
1	Playful	10
2	Sing	10
1	Bowl	30
1	SQL	40

I'm trying to write a order by which will sort the persons by skills alphabetically but nulls last.

Looking for something like this:

SELECT distinct
  p.*,
  STUFF(
    (SELECT ',' + ps.SKILL
      FROM PersonSkill ps
      WHERE ps.PERSON_ID = p.ID
      ORDER BY ps.SORT
      FOR XML PATH('')
    ), 1, 1, '') sortRule
FROM Person p
ORDER BY IIF(sortRule is null, 1, 0) asc, sortRule asc

But for some reason I can't use sortRule inside an IIF or a case operation within ORDER BY as it's giving me this error: Invalid column name 'sortRule'.

If I remove the STUFF sortRule from the select statement it will tell me that it is required to be there when using alongside distinct. I also can't just copy the STUFF down to the order by as it will say: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Answer 1

As you're performing string aggregation, then you should be using a GROUP BY, not DISTINCT. As for sorting on sortRule, you can't wrap a column's alias in an expression in the ORDER BY. One method, therefore, is to use a CTE:

WITH CTE AS(
    SELECT p.Id,
           p.[Name],
           STUFF((SELECT ',' + ps.SKILL
                  FROM dbo.PersonSkill ps
                  WHERE ps.PERSON_ID = p.ID
                  ORDER BY ps.SORT
                  FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'') AS sortRule
    FROM dbo.Person p
    GROUP BY p.Id,
             p.[Name])
SELECT *
FROM CTE
ORDER BY CASE WHEN sortRule IS NULL THEN 1 ELSE 0 END,
         sortRule;

db<>fiddle