I need to show the most recent (per CaptureTime) for each Device showing the Loc and In values but when I try to do this:
SELECT Device, MAX(Loc) AS Loc, MAX(CaptureTime) AS CaptureTime, MAX(Here) AS In FROM dbo.vrtEventSim GROUP BY Device HAVING (Device = Device)
I get "Operand data type bit is invalid for max operator."
So I try this:
SELECT Device, MAX(CaptureTime) AS CaptureTime, MAX(Loc) AS Loc, MAX(CAST(Here AS Int)) AS Here FROM dbo.vrtEventSim GROUP BY Device HAVING (Device = 808) which returns: Device CaptureTime Loc Here 808 20:38:21 55 1
SELECT TOP 1 * FROM dbo.vrtEventSim WHERE Device = 808 ORDER BY CaptureTime Desc which returns: Device Loc CaptureTime Here 808 5 20:38:21 0
So the Group query returns a different Here value than the vanilla query.
If you have two rows:
The max time is 20:38:21 and the max here is 1.
>>HAVING (Device = Device)
I don't know what this is supposed to be doing. Just because you have a GROUP BY, you don't have to have a HAVING.