Link to home
Create AccountLog in
Avatar of Bill Park
Bill ParkFlag 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.
Avatar of slightwv (䄆 Netminder)
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.
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
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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.


You should be able to implement that within the CTE.
Thanks, very much.