Assistance with order by "order by case" statement-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You cant use a Case expression in your Order By clause if it is not present in your select clause. Instead of using Distinct, you could also try using Group By instead.

CodePudding user response：

As others have stated, you need the CASE statement in your select list.

However: If you are using this in SSRS you do not need the ORDER BY clause, you can just add the case statement to the select list. SSRS will order by whatever the tablix row groups are defined by anyway.

So, add the CASE statement to your select list with an alias (e.g. AdmissionsStatusOrder), you can remove the ORDER BY clause as this is not really doing much, then in your report, order by your new AdmissionsStatusOrder column.