Get last log line per unique host from table

Question

Hi,

I have inherited an application that logs the results of certain daily commands that run on multiple hosts to an MS-SQL table. Now I've been asked to provide a view/query that displays the last log line per host to get an overview of the last results.

The table is similar to this:

------------------------------
|HOST    |LAST_RUN   |RESULT |
------------------------------
|SERVER1 |13-07-2009 |OK     |
|SERVER2 |13-07-2009 |Failed |
|SERVER1 |12-07-2009 |OK     |
|SERVER2 |12-07-2009 |OK     |
|SERVER3 |11-07-2009 |OK     |
------------------------------

In this case the query should output:

------------------------------
|HOST    |LAST_RUN   |RESULT |
------------------------------
|SERVER1 |13-07-2009 |OK     |
|SERVER2 |12-07-2009 |Failed |
|SERVER3 |11-07-2009 |OK     |
------------------------------

...as these are the last lines for each of the hosts.

I realise that it might be something simple I'm missing, but I just can't seem to get it right :-(

Thanks, Mark.

Answer 1

Select Host, Last_Run, Result from 
(
   select ROW_NUMBER() OVER (PARTITION BY Host ORDER BY Last_Run DESC) AS row_number,
   Host, Last_Run, Result from Table1
) tempTable
where row_number = 1

Answer 2

Here's a quick version:

SELECT lt.Host, lt.Last_Run, lt.Results
 from LogTable lt
  inner join (select Host, max(Last_Run) Last_Run
               from LogTable
               group by Host) MostRecent
   on MostRecent.Host = lt.Host
    and MostRecent.Last_run = lt.Last_Run

This should work in most any SQL system. The ranking functions in SQL Server 2005 or 2008 might work a bit better.

Answer 3

 select host, max (last_run) from t group by host

Gets you the host and last_run you want. Then

 (select host as h, last_run as lr, result as r from t) inner join (select host as h_max, max (last_run) as lr_max from t group by host) on h=h_max, lr=lr_max

Forgive my sql if not exact -- not trying it or looking it up, but you get the idea.

Answer 4

I think there's a typo at SERVER2's LAST_RUN date.

This provides the same result as all the other great answers without the use of a subquery:

select t."HOST"
       , t."LAST_RUN"
       , t."RESULT"
from   yourtable t
       left outer join yourtable t2
       on t."HOST" = t2."HOST"
    and
       t2."LAST_RUN" > t."LAST_RUN"
where  t2."RESULT" is null;

And the resultset looks like this:

|HOST    |LAST_RUN   |RESULT |
------------------------------
|SERVER1 |13-07-2009 |OK     |
|SERVER2 |13-07-2009 |Failed |
|SERVER3 |11-07-2009 |OK     |