基于最新时间戳的MySQL选择记录组 [英] MySQL Select Group of Records Based on latest timestamp

问题描述

我有一个例程，每隔几个小时运行一次，该例程会在用于记录的表中创建多个条目.我需要做的是选择具有最新时间戳且具有共同帐户ID的所有记录.像这样:

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 

我遇到的问题是我不能说LIMIT 5之类的东西，因为在每个例程间隔创建的记录数可能不同.因此，例如，当例程在上午10点运行时，它可能在下午2点创建6个表条目，而仅创建4个表条目.

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?

推荐答案

假设您的意思是Table_Logs表中的多个条目可能具有相同的时间戳，并且您想返回最近输入的每个条目，则需要使用GROUP BY:

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等是要在Table_Logs中返回的字段.

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

以下是一些示例 SQL提琴进行尝试.

Here is some sample SQL Fiddle to try out.

祝你好运.

这篇关于基于最新时间戳的MySQL选择记录组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！