I need to build a stored procedure that produces a snapshot of the last status of a Device in telemetry:
CREATE TABLE [dbo].[vrtEvents](
[Device] [int] NULL,
[Loc] [tinyint] NULL,
[CaptureTime] [time](0) NULL,
[Here] [bit] NULL
)
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
go
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.
What am I missing?
Thank you.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.