4

I have a routine that runs every few hours that creates several entries in a table used for logging. What I need to do is select all the records with the most recent timestamp that have a common account id. Something like this:

SELECT * 
FROM TABLE_logs 
WHERE ACCOUNT_ID='12345' 
ORDER BY TIMESTAMP DESC

Where I'm stuck is that I can't say LIMIT 5 or something like that because the number of records created at each routine interval could be different. So, for example, when the routine runs at 10AM, it may create 6 table entries and only 4 table entries at 2PM.

Is there a way to select the grouping of the latest records without knowing how many there are?

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Jason
  • 1,105
  • 3
  • 16
  • 30

2 Answers2

3

Assuming you mean multiple entries in your Table_Logs table could have the same timestamp and you want to return each of those that were entered most recently, you need to use GROUP BY:

SELECT Field1, Field2, Max(TimeStamp) maxTime
FROM Table_Logs
WHERE Account_Id = '12345'
GROUP BY Field1, Field2

Field1, etc. are the fields you want to return in Table_Logs.

Here is some sample SQL Fiddle to try out.

Good luck.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • 1
    That worked perfectly - thank you! One quick question - right now the timestamps for each interval are identical (i.e., 2013-01-27 08:00:01). If for some reason the entries span a few seconds (i.e, 2013-01-27 08:00:01, 2013-01-27 08:00:02) will this still work or will only only select the 08:00:02 entry? – Jason Jan 27 '13 at 14:39
  • I just ran it in Fiddle and changed a few of the timestamps by a few seconds and it seemed to work! – Jason Jan 27 '13 at 14:40
0

I think what you want is to group by the timestamp. Assuming that all the ones entered at 10am had the same timestamp, and 2 pm, ditto, would be something like:

  SELECT timestamp, Field1, Field2
    FROM Table_Logs
GROUP BY timestamp

If your timestamps are too precise, simply do a substring:

  SELECT LEFT(timestamp, 5), Field1, Field2
    FROM Table_Logs
GROUP BY LEFT(timestamp, 5)

Assuming the timestamp is a string (log file). If not, you have to stringify it.

Rob
  • 11,446
  • 7
  • 39
  • 57