Assistance with order by "order by case" statement

Question

I wonder if someone could help me with a problem I have getting an "order by case" statement to work. Below is the SQL I have embedded in an SSRS report but when I run it I receive the following error message:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified. (Microsoft SQL Server, Error: 145)

   SELECT DISTINCT ppl.txtSchoolID,
       ppl.txtSurname,
       ppl.txtForename,
       ppl.txtGender,
       ppl.txtReligion,
       CASE COALESCE(pplFamily.intFamily, 0)
           WHEN 0 THEN 'N'
           ELSE 'Y'
       END AS [HasSiblings],
       ppl.txtDOB,
       DATEDIFF(yy, ppl.txtDOB, GETDATE()) - 
           CASE WHEN (MONTH(ppl.txtDOB) > MONTH(GETDATE()))
                  OR (MONTH(ppl.txtDOB) = MONTH(GETDATE()) 
                  AND DAY(ppl.txtDOB) > DAY(GETDATE())) THEN 1 
                ELSE 0 
           END AS [Age],
       CASE COALESCE(Internationals.txtValue, 'false')
             WHEN 'true' THEN 'Y'
             WHEN 'false' THEN 'N'
       END AS [International],
       ppl.txtType,
       ppl.intEnrolmentNCYear,
       schYears.txtYearName,
       ppl.txtEnrolmentTerm,
       ppl.intEnrolmentSchoolYear,
       ppl.txtBoardingHouse,
       ppl.txtNationality,
       ppl.txtAdmissionsStatus,
       ppl.txtEnrolmentAcademicHouse
FROM dbo.TblPupilManagementPupils AS ppl
     LEFT OUTER JOIN
     dbo.TblPupilManagementCustomFieldValue AS Internationals
     ON ppl.txtSchoolID = Internationals.txtSchoolId
     AND Internationals.intCustomFieldId = 14
     LEFT OUTER JOIN
     dbo.TblSchoolManagementYears AS schYears
     ON ppl.intEnrolmentNCYear = schYears.intNCYear
     LEFT OUTER JOIN
     (SELECT txtSchoolID, intFamily
      FROM dbo.TblPupilManagementPupils) AS pplFamily
     ON     pplFamily.intFamily = ppl.intFamily
        AND pplFamily.txtSchoolID <> ppl.txtSchoolID
WHERE (ppl.intEnrolmentSchoolYear IN (@AcademicYear))

ORDER BY CASE WHEN ppl.txtAdmissionsStatus = 'Registered' THEN 1
              WHEN ppl.txtAdmissionsStatus = 'Interviewed' THEN 2
              WHEN ppl.txtAdmissionsStatus = 'Offered' THEN 3
              WHEN ppl.txtAdmissionsStatus = 'Accepted' THEN 4
              ELSE 5 END

Is anyone able to tell me what I am doing wrong?

Many thanks in advance.

Stuart

Answer 1

Since DISTINCT and GROUP BY kind of work similarly, you'd need everything appearing in the order by to b available in the SELECT part.

The whole case statement

CASE WHEN ppl.txtAdmissionsStatus = 'Registered' THEN 1
              WHEN ppl.txtAdmissionsStatus = 'Interviewed' THEN 2
              WHEN ppl.txtAdmissionsStatus = 'Offered' THEN 3
              WHEN ppl.txtAdmissionsStatus = 'Accepted' THEN 4
              ELSE 5 END

is a new calculated column and is not available in the SELECT DISTINCT list. You'd need to add that there. Note that this new column is different form ppl.txtAdmissionsStatus which is available.

So you need to put that case statement in the SELECT part like below.

you can also refer to multiple answers here on ORDER BY items must appear in the select list if SELECT DISTINCT is specified

 SELECT DISTINCT ppl.txtSchoolID,
       ppl.txtSurname,
       ppl.txtForename,
       ppl.txtGender,
       ppl.txtReligion,
       CASE COALESCE(pplFamily.intFamily, 0)
           WHEN 0 THEN 'N'
           ELSE 'Y'
       END AS [HasSiblings],
       ppl.txtDOB,
       DATEDIFF(yy, ppl.txtDOB, GETDATE()) - 
           CASE WHEN (MONTH(ppl.txtDOB) > MONTH(GETDATE()))
                  OR (MONTH(ppl.txtDOB) = MONTH(GETDATE()) 
                  AND DAY(ppl.txtDOB) > DAY(GETDATE())) THEN 1 
                ELSE 0 
           END AS [Age],
       CASE COALESCE(Internationals.txtValue, 'false')
             WHEN 'true' THEN 'Y'
             WHEN 'false' THEN 'N'
       END AS [International],
       ppl.txtType,
       ppl.intEnrolmentNCYear,
       schYears.txtYearName,
       ppl.txtEnrolmentTerm,
       ppl.intEnrolmentSchoolYear,
       ppl.txtBoardingHouse,
       ppl.txtNationality,
       ppl.txtAdmissionsStatus,
       ppl.txtEnrolmentAcademicHouse, 
CASE WHEN ppl.txtAdmissionsStatus = 'Registered' THEN 1
              WHEN ppl.txtAdmissionsStatus = 'Interviewed' THEN 2
              WHEN ppl.txtAdmissionsStatus = 'Offered' THEN 3
              WHEN ppl.txtAdmissionsStatus = 'Accepted' THEN 4
              ELSE 5 END as AdmissionStatusKey
FROM dbo.TblPupilManagementPupils AS ppl
     LEFT OUTER JOIN
     dbo.TblPupilManagementCustomFieldValue AS Internationals
     ON ppl.txtSchoolID = Internationals.txtSchoolId
     AND Internationals.intCustomFieldId = 14
     LEFT OUTER JOIN
     dbo.TblSchoolManagementYears AS schYears
     ON ppl.intEnrolmentNCYear = schYears.intNCYear
     LEFT OUTER JOIN
     (SELECT txtSchoolID, intFamily
      FROM dbo.TblPupilManagementPupils) AS pplFamily
     ON     pplFamily.intFamily = ppl.intFamily
        AND pplFamily.txtSchoolID <> ppl.txtSchoolID
WHERE (ppl.intEnrolmentSchoolYear IN (@AcademicYear))

ORDER BY AdmissionStatusKey