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 using ROW_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.
SELECT
dv.Name,
hb.*
FROM [Devices] as dv
OUTER APPLY (
SELECT TOP 1 *
FROM Heartbeats
WHERE Heartbeats.DeviceID = dv.ID
ORDER BY DateEntered DESC
) hb
WHERE ISNULL(hb.DateEntered, '1900-01-01T00:00') < '2013-03-04T00:00'
See Books Online for the finer points of OUTER APPLY
vs. CROSS APPLY
(it's much like OUTER JOIN
vs. INNER JOIN
). It was always such a pain doing queries like this in SQL Server 2000 where you didn't have OUTER/CROSS APPLY
or the ROW_NUMBER()
function.
Window Functions, or the OVER() Clause
It appears that what you are looking for is a window function or an OVER()
clause.
In your original example, you are trying to use two max()
conditions, which doesn't work, because when you try to then apply GROUP BY
, you can have a condition where the max of the first column ID
and the second column startdate
aren't in the same row, and so then GROUP BY
simply can't be understood.
However, if you are looking to extract the max over a grouping, and you can define that grouping, and you want to obtain the 'column-wise' maximum for more than one column for that grouping (as you seem to want to do), then here's the solution.
CREATE TABLE test (
jobcode integer,
id integer,
a_startdate datetime,
a_enddate datetime,
b_startdate datetime,
b_enddate datetime);
INSERT INTO test VALUES (513801, 7136, '11-01-2011', '12-31-9998', '11-01-2011', '12-31-9998');
INSERT INTO test VALUES (513801, 7137,'04-26-2014', '12-31-9998','04-26-2014', '12-31-9998');
I first made this table to recreate your input data in the picture. I then applied this query.
SELECT jobcode, max(id) OVER (PARTITION BY jobcode),
max(a_startdate) OVER(PARTITION BY jobcode),
a_enddate, b_startdate, b_enddate FROM test;
Try out this SQL Fiddle, and see if it gives the results you're looking for. I did the best I could with the description I had.
Best Answer
Top is only giving you one record because you're asking for the top '1' row. If you want all, just change it to: