troubleshooting Question

SQL Server - Simple Group by question.

Avatar of Bill Park
Bill ParkFlag for United States of America asked on
SQLMicrosoft SQL Server
6 Comments1 Solution12 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Brian Crowe
Database Manager
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros