Feeling a bit confused here, I have a query running against SQL Server where I am trying to return a single row per trans_id and action number (they may be repeated). The trans column would be different in the repeated rows so I only ever want to return the highest (max value) of the trans column to give me the single row per trans_id and action number.

This seems to do the trick

SELECT DT.trans_id,
       MAX(DT.trans),
       DA.ACTION_NO,
       DT.[Title] AS [Case Title],
       DA.[Description] AS [Action Description],
       DA.ACTION_TIME_LIMIT AS [Action Deadline],
       DA.Performed AS [Action Perfomed]
FROM synergi.stg_D_TRANS DT
     INNER JOIN EQDW_Stg.synergi.stg_D_ACTION DA ON DA.TRANS = (SELECT MAX(TRANS)FROM synergi.stg_D_TRANS WHERE trans_id = DT.TRANS_ID)
WHERE DT.TRANS_ID != 0
GROUP BY DT.TRANS_ID,
         DA.ACTION_NO,
         DT.TITLE,
         DA.DESCRIPTION,
         DA.PERFORMED,
         DA.ACTION_TIME_LIMIT;

However, when I add in this specific column, DT.TRANS_DATE I get an additional 18 rows because it is giving me one of the trans_id with 2 different trans numbers instead of just the rows with the max. Why would adding in the date column affect this when I have numerous other columns from the same table that adding/removing don’t seem to change the result at all.

SELECT DT.trans_id,
       MAX(DT.trans),
       DA.ACTION_NO,
       DT.[Title] AS [Case Title],
       DA.[Description] AS [Action Description],
       DA.ACTION_TIME_LIMIT AS [Action Deadline],
       DA.Performed AS [Action Perfomed],
       DT.TRANS_DATE
FROM synergi.stg_D_TRANS DT
     INNER JOIN EQDW_Stg.synergi.stg_D_ACTION DA ON DA.TRANS = (SELECT MAX(TRANS)FROM synergi.stg_D_TRANS WHERE trans_id = DT.TRANS_ID)
WHERE DT.TRANS_ID != 0
GROUP BY DT.TRANS_ID,
         DA.ACTION_NO,
         DT.TITLE,
         DA.DESCRIPTION,
         DA.PERFORMED,
         DA.ACTION_TIME_LIMIT,
         DT.TRANS_DATE;