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;