55882

get latest record for each ID

Question:

I would like to get the latest record for each server.

Here is some example data:

TimeGenerated SourceName ComputerName Message 2014-11-22 21:48:30 Windows Update Agent Server1 Update Failed 2014-11-22 21:42:30 Windows Update Agent Server2 Update Failed 2014-11-22 21:45:30 Windows Update Agent Server2 Update Failed 2014-11-22 21:43:30 Windows Update Agent Server1 Update Failed

Desired Output:

TimeGenerated SourceName ComputerName Message 2014-11-22 21:48:30 Windows Update Agent Server1 Update Failed 2014-11-22 21:45:30 Windows Update Agent Server2 Update Failed

I tried:

SELECT * FROM TABLE GROUP BY ComputerName ORDER BY TimeGenerated ASC

But that outputs inconsistent results and does not give me the latest in most cases.

I also tried some sub queries, but failed miserably.

Answer1:

SELECT * FROM yourtable INNER JOIN ( SELECT MAX(timeGenerated) as maxtime, ComputerName FROM yourtable GROUP BY ComputerName ) AS latest_record ON (yourtable.timeGenerated = maxtime) AND (latest_record.ComputerName = yourtable.ComputerName)

Inner query gets the latest timestamp for every computer name. The outer query then joins against that query result to fetch the rest of the fields from the table, based on the time/computername the inner query finds. If you have two events logged with identical max times, you'd get two records for that computername.

Answer2:

Try this:

SELECT ComputerName, Max(date) FROM TABLE GROUP BY ComputerName

Answer3:

I you only use a GROUP function, you can only display the columns which are part of that SELECT statement. If you need more data displayed, you need to work with a subquery. For example, if you want to "select *", you can't just use only a GROUP BY and no subquery. So, it depends what you want to display.

Recommend

  • XJC javaType adapter in complexType
  • How to fix the Google Map API when print the whole page?
  • SSRS returning different results than the stored procedure
  • How to hide the cursor in windows when dragging and dropping (possibly in python, or another languag
  • NoMethodError in Controller#action - undefined method `keynote' for #
  • Calling java project from Mathematica
  • PHP file_exists() anomaly
  • How to sort a same column both in asc order and desc order
  • Warning: strpos() expects parameter 1 to be string, resource
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • crash in __tcf_0
  • SQL - Select lowest values with group by and order by?
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • For loop with if condition on multiple R functions
  • RxJava debounce by arbitrary value
  • D3 get axis values on zoom event
  • Is playing sound in Javascript performance heavy?
  • C: Incompatible pointer type initializing
  • Content-Length header not returned from Pylons response
  • Breeze - Deleted Items nav properties bug
  • Linq Objects Group By & Sum
  • Insert into database using onclick function
  • Read text file and split every line in MSBuild
  • Optimizing database types to compact database (SQLite)
  • javaw.exe and eclipse startup problems
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Knitr HTML Loop - Some HTML output, some R output
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • How to get next/previous record number?
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • PHP: When would you need the self:: keyword?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Buffer size for converting unsigned long to string
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Trying to get generic when generic is not available
  • embed rChart in Markdown
  • need help with bizarre java.net.HttpURLConnection behavior
  • reshape alternating columns in less time and using less memory