从表中选择最近的记录 [英] Select recent records from table

问题描述

我有一个包含超过500,000条记录的动态表。我想选择最近添加的记录。

I have a dynamic table consist of more than 500,000 records. I want to select records which are recently added.

epc                  toX toY observTime
000000000000000000000041    2.41    -6.22   2018-01-12 17:04:00.070
000000000000000000000041    3.45    -7.26   2018-01-12 17:14:30.090
000000000000000000000041    2.67    -6.48   2018-01-12 17:27:00.073
000000000000014028924001    0.73    -1.86   2018-01-12 17:31:30.083
000000000000014028924001    0.64    -1.89   2018-01-12 17:42:00.077
11110001                    1.96    -4.73   2018-01-12 17:31:30.083
11110001                    2.02    -4.68   2018-01-12 17:42:00.083
11110001                    2.1 -4.62   2018-01-12 17:21:00.090

我需要这样的输出

I need output like this

epc	                toX	toY	observTime
000000000000000000000041	2.67	-6.48	2018-01-12 17:27:00.073
000000000000014028924001	0.64	-1.89	2018-01-12 17:42:00.077
11110001	                2.02	-4.68	2018-01-12 17:42:00.083

简而言之，我想根据'observTime'选择最近的记录。

我提到了动态表，这意味着数据会定期存储在表中，我需要选择记录最近的observTime



我尝试过：

In short, I want to select record which is recent one according to 'observTime'.
I mentioned the dynamic table which means data is getting stored in table periodically and I need to select record which has recent observTime

What I have tried:

SELECT epc, toX, toY, toFacility, toFloor, toZone, observTime
FROM  dbo.tbl_ItemHistoryInfo AS t1
WHERE ((SELECT COUNT(*) 
        FROM dbo.tbl_ItemHistoryInfo AS t2
        WHERE (epc = t1.epc) AND (observTime > t1.observTime)) = 0)

我使用此查询创建了一个视图。但我没有得到最近的记录。

你能提供一个查询来帮助我，我会得到最近的记录吗？我必须考虑查询的时间复杂性。我有超过500,000条记录。那么这对它来说是否是一个很好的解决方案？

I created a view using this query. But I am not getting recent records.
Can you please help me by providing a query in which I will get recent records? As well as I have to consider time complexity of the query. I have more than 500,000 records. So whether this will be the good solution for it?

推荐答案

尝试类似

SELECT TOP 100 * FROM MyTable ORDER BY MyDateColumn DESC

听起来你想要每个epc的最新记录。有不同的方法，这是使用派生表的一种方式：

It sounds like you want the most recent record for each epc. There are different ways of doing it, here is one way using a derived table:

SELECT DISTINCT t.epc, t.observtime
FROM table1 t
INNER JOIN (
  -- this derived table will give you the epc and the most recent time
  -- which you can then use to rejoin to your main table to get the rest of the fields
  SELECT epc, MAX(observTime) AS MaxobServTime
  FROM table1 
  GROUP BY epc
) x ON t.epc = x.epc AND t.ovservTime = x.MaxObservTime

这篇关于从表中选择最近的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！