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
Best Answer
You can do this pretty easily with
OUTER APPLY
(if you're on 2005 or newer). Note that there may be better performing ways of achieving the result, such as usingROW_NUMBER()
- check execution plans if in doubt. Also,SELECT *
is lazy and inadvisable; I'm just doing it here for illustrative purposes, and because I don't know the real structure of the Heartbeats table.See Books Online for the finer points of
OUTER APPLY
vs.CROSS APPLY
(it's much likeOUTER JOIN
vs.INNER JOIN
). It was always such a pain doing queries like this in SQL Server 2000 where you didn't haveOUTER/CROSS APPLY
or theROW_NUMBER()
function.