Avatar of Bill Park
Bill Park
Flag for United States of America asked on

SQL Server - Simple Group by question.

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.
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Bill Park

8/22/2022 - Mon
slightwv (䄆 Netminder)

The MAX(here) for the device is different than the MAX(CaptureTime).

If you have two rows:
808,20:38:21,0
808,11:11:11,1

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.
Brian Crowe

If I am interpreting you question correctly, I think you're looking for something like this...

WIITH cteEventOrdered AS
(
   SELECT Device,
      Loc,
      CaptureTime,
      Here,
      ROW_NUMBER() OVER(PARTITION BY Device ORDER BY CaptureTime DESC) AS RowNumber
   FROM dbo.vrtEvents
)
SELECT *
FROM cteEventOrdered
WHERE RowNumber = 1
   AND Device = 808   --optional 

Open in new window

ASKER CERTIFIED SOLUTION
Brian Crowe

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Bill Park

ASKER
VERY close.
and you got everything I described.

I would like to take a snapshot as of when the sp is run.
When I try to add the conditional -
 
AND CaptureTime < CONVERT(Time(0),GETUTCDATE())

-- or GETDATE()

to either of the WHEREs, the results don't change.

How should I implement that?

Thank you.


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Brian Crowe

You should be able to implement that within the CTE.
Bill Park

ASKER
Thanks, very much.