As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.
As stated, this query does return me 95% of the data I need --
SELECT dv.Name
,MAX(hb.[DateEntered]) as DE
FROM
[Devices] as dv
INNER JOIN
[Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';
Is there a way to achieve a the same result (for each Device, select the TOP Heartbeat ordered DESC by DateEntered) but also select the entire row from the [Heartbeats]
table? Right now, I only get the DateTime
for that row.
If I include the additional columns in the GROUP BY
clause, I can then add them to the select; but then I get multiple rows per [Devices]
row which I don't want. It sounds odd, but what I basically want to do is do a query against [Devices]
and then do a for...each
over that set and add the top [Heartbeats]
row for that [Devices]
row. Is that possible?
update This is the structure of the Heartbeats table:
CREATE TABLE [dbo].[Heartbeats](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DeviceID] [int] NOT NULL,
[IsFMSFMPUp] [bit] NOT NULL,
[IsFMSWebUp] [bit] NOT NULL,
[IsPingUp] [bit] NOT NULL,
[DateEntered] [datetime] NOT NULL,
CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats]
(
[DateEntered] ASC,
[DeviceID] ASC
)
INCLUDE ( [ID],
[IsFMSFMPUp],
[IsFMSWebUp],
[IsPingUp]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HeartbeatDeviceIndex] ON [dbo].[Heartbeats]
(
[DeviceID] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Default [DF_Heartbeats_DateEntered] Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats] ADD CONSTRAINT [DF_Heartbeats_DateEntered] DEFAULT (getdate()) FOR [DateEntered]
GO
/****** Object: ForeignKey [FK_Heartbeats_Devices] Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats] WITH CHECK ADD CONSTRAINT [FK_Heartbeats_Devices] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Devices] ([ID])
GO
ALTER TABLE [dbo].[Heartbeats] CHECK CONSTRAINT [FK_Heartbeats_Devices]
GO