CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-bystuff
Jeggy
Jeggy

Reputation: 1650

How to order by nulls-last by a linked table

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.

Upvotes: 0

Views: 89

Answers (1)

Thom A
Thom A

Reputation: 95579

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

Upvotes: 1

Related Questions