SQL query seconds to date (Ssms12)

Question

on the stackoverflow site in fount Thad select CONVERT(CHAR(10), DATEADD(second, MAX(60), 0), 108) will result in 00:01:00 (that's true btw :-) )

now I want to integrate this in the following query (for Ssms12):

SELECT Run.TaskName,history.runtime
,CONVERT(CHAR(10), DATEADD(second, MAX(history.runtime), 0), 108)

FROM dbo.history
INNER JOIN Run
ON dbo.history.TaskID=Run.TaskID

whatever I try, I can't get this to work in the above example, does anyone have an idea how to fix this?

error: Column 'Run.TaskName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

You have to group by other columns where you are not using aggregate function:

SELECT Run.TaskName,history.runtime,
   CONVERT(CHAR(10), DATEADD(second, history.runtime, 0), 108)
FROM dbo.history
INNER JOIN Run ON dbo.history.TaskID=Run.TaskID

You are using MAX for runtime so you can do like :

SELECT Run.TaskName,MAX(history.runtime) runtime,
    CONVERT(CHAR(10), DATEADD(second, MAX(history.runtime), 0), 108)
FROM dbo.history
INNER JOIN Run ON dbo.history.TaskID=Run.TaskID
GROUP BY Run.TaskName

It means either you have to remove MAX from runtime or you have to use group by for rest of columns.