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.
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
Last Comment
Bill Park
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
ASKER CERTIFIED SOLUTION
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.
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.
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.
ASKER
Thanks, very much.
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.