CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bymax
Hugh
Hugh

Reputation: 15

SQL Max() in SELECT causes values in row to be incorrect

The SQL query:

WITH Instructors AS (
SELECT DISTINCT 
dimUser.EmpFK, 
Usr.Usr_Name AS UserID, 
dimUser.EmpFullName3, 
dimUser.PrimaryOrgName,
dimUser.EmpCity,
dimUser.EmpPhn1,
dimUser.EmpUrl,
dimUser.PrimaryJobName,
dimUser.EmpState,
dimUser.EmpAdd2

FROM factResourceInstructor FRI
LEFT JOIN dimUser 
ON dimUser.ID = FRI.InstID
LEFT JOIN Iwc_Usr Usr 
ON dimUser.EmpFK = Usr.Usr_empFK)

SELECT
MAX(Instructors.EmpFullName3) as N'Name',
MAX(Instructors.PrimaryOrgName) as N'Group/Function',
MAX(Instructors.EmpCity) as N'Region',
MAX(Instructors.EmpPhn1) as N'Division',
MAX(Instructors.empurl) as N'Office',
MAX(Instructors.PrimaryJobName) as N'Job',
class.ActivityName as N'Class Name',
dimActivity.ActivityLabel as N'ActivityType',
MAX(Activity.ActivityName) as N'Session Name',
--dimActivity.EstDurHours as N'Estimated Duration',
CASE 
  WHEN SUM(Attempt.ElapsedSeconds) IS NOT NULL 
  THEN SUM(Attempt.ElapsedSeconds) / 3600 ELSE 0 
END AS 'Estimated Duration',
Activity.StartDt as N'Activity Start Date (UTC)',
DateAdd(millisecond,  
        ISnull((select RawOffset 
                from tbl_lms_TimeZoneData 
                where TimeZone_FK = (MAX(dimActivity.TimeZoneFK))
                )
        ,0) 
, DateAdd(millisecond,  
         ISnull((select Offset 
                 from tbl_lms_ConvertTimeZoneData 
                 where TimeZone_FK = (MAX(dimActivity.TimeZoneFK)) 
                 and MAX(dimActivity.StartDt) between StartDate and EndDate),
         0)
, MAX(dimActivity.StartDt))) as N'Activity Start Date'

FROM Instructors
LEFT JOIN TBL_TMX_Attempt Attempt 
ON Instructors.EmpFK = Attempt.EmpFK
LEFT JOIN TBL_TMX_Activity Activity 
ON Attempt.ActivityFK = Activity.Activity_PK
LEFT JOIN dimActivity 
ON Activity.Activity_PK = dimActivity.ActivityFK
INNER JOIN dimActivity class 
on class.ActivityFK = dimActivity.RootActivityFK

GROUP BY class.ActivityName
, dimActivity.ActivityLabel
, Activity.StartDt
ORDER BY Name

returns a list of users and jobs. Each user can appear on more than one row but occasionally a user can have a different value in the 'Job' column in different rows. Why is this happening and how can I stop it? I am using Microsoft SQL Server if that information helps. Unfortunately due to the software that I need to use with the query, I am unable to use sub-queries.

I am not experienced with SQL and also did not originally write this query. Thanks for the help.

Upvotes: 0

Views: 152

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

You have this column in the GROUP BY list:

Instructors.PrimaryJobName

Yet this column appears in your SELECT list as an aggregate (which means it shouldn't be in the GROUP BY list, or it shouldn't be aggregated - we have no idea which, except that MAX() on a value that is almost certainly string doesn't seem to make a whole lot of sense).

Try removing that column from the GROUP BY list, or not applying MAX() to it in the SELECT list, and see if the results are correct.

Upvotes: 1

Related Questions