Max and group by

[EmmaJean]

Good morning!

I have created a query in Reporting Services 2003/sql server 2000 that shows all data related to the max timestamp. I want to add in the action_desc to the row that corresponds to that date.
If I add that field, I get the error.."not included in the aggregate or group by clause"
If I include it in the group by clause..."the text, ntext and image data types cannot be compared or sorted..."

Help please : ) Thank you!

SELECT MAX(DATEADD(ss, AHD.act_log.time_stamp - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102))) AS [Time Stamp],
AHD.ctct.c_first_name + ' ' + AHD.ctct.c_last_name AS Analyst, AHD.call_req.ref_num AS Request, AHD.ctct.id, DATEADD(ss,
AHD.call_req.open_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Date Opened], ctct_3.c_last_name AS [Group], DATEADD(ss,
AHD.call_req.close_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Date Closed], AHD.act_log.type
FROM AHD.call_req LEFT OUTER JOIN
AHD.act_log ON AHD.call_req.persid = AHD.act_log.call_req_id LEFT OUTER JOIN
AHD.ctct ctct_3 ON AHD.call_req.group_id = ctct_3.id LEFT OUTER JOIN
AHD.ctct ON AHD.call_req.assignee = AHD.ctct.id
WHERE (ctct_3.c_last_name = 'LPCH IDX') AND (AHD.call_req.active_flag = 1) AND (AHD.act_log.type = 'ST')
GROUP BY AHD.ctct.c_first_name + ' ' + AHD.ctct.c_last_name, AHD.call_req.ref_num, AHD.ctct.id, DATEADD(ss, AHD.call_req.open_date - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), ctct_3.c_last_name, DATEADD(ss, AHD.call_req.close_date - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)), AHD.act_log.type
ORDER BY DATEADD(ss, AHD.call_req.open_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) DESC

 

[RiverGuy]

I prefer not to GROUP BY everything in order to get the latest (or earliest or lowest or highest) row.

Instead, get your key column(s), your MAX aggregate, and then join that to the table. For example:

SELECT 
DATEADD(ss, MaxDate - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Time Stamp],
AHD.ctct.c_first_name + ' ' + AHD.ctct.c_last_name AS Analyst, AHD.call_req.ref_num AS Request, AHD.ctct.id, DATEADD(ss,
AHD.call_req.open_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Date Opened], ctct_3.c_last_name AS [Group], DATEADD(ss,
AHD.call_req.close_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Date Closed], AHD.act_log.type
FROM AHD.act_log
INNER JOIN
	(SELECT KeyColumn,  MAX(AHD.act_log.time_stamp - 18000) AS MaxDate
	FROM AHD.act_log
	GROUP BY KeyColumn) MaxDates
		ON AHD.act_log.KeyColumn = MaxDates.KeyColumn AND AHD.act_log.time_stamp = MaxDates.MaxDate 
INNER JOIN AHD.call_req ON AHD.call_req.persid = AHD.act_log.call_req_id
LEFT OUTER JOIN AHD.ctct ctct_3 ON AHD.call_req.group_id = ctct_3.id 
LEFT OUTER JOIN AHD.ctct ON AHD.call_req.assignee = AHD.ctct.id
WHERE (ctct_3.c_last_name = 'LPCH IDX') AND (AHD.call_req.active_flag = 1) AND (AHD.act_log.type = 'ST')
ORDER BY MaxDate DESC