通过带有多个时间戳的唯一ID获取最新的表条目

本文介绍了通过带有多个时间戳的唯一ID获取最新的表条目的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在Oracle数据库中有一个包含机器事务的表，我需要找到对产品执行的最后操作的时间.

I have a table in an Oracle database with machine transactions, and I need to find the time of the last action done to a product.

通过唯一的ID跟踪产品，并且机器操作均带有时间戳.产品不会总是打在每台机器上，否则我可以从最后一台机器上静态地调用时间戳.我希望仅从特定过程中获取数据，并且在2016年1月1日之后，其逻辑已经在其他查询中起作用.

The products are tracked by a unique id and the machine actions are all time-stamped. The product will not always hit every machine, or else I could just statically call the time stamp from the last machine. I am looking to get data from only a certain process, and after 1/1/2016, the logic of which already works in other queries.

到目前为止，我的查询尝试都在下面，它返回所有条目，因此我需要减少到最后一个操作的内容:

My query attempt thus far is below, this returns all entries, so I need something to cut down to just the last action:

select UniqueID, TimeStamp, MachineName from TransactionTable where ActionPerformed like 'action' and TimeStamp > '1/1/2016' group by UniqueID, TimeStamp order by UniqueId, TimeStamp desc

推荐答案

您可以为按日期desc排序的每个唯一ID生成一个序列:

You can generate a sequence for each UniqueID ordered by the date desc:

SELECT * FROM (SELECT UniqueID, TimeStamp, MachineName, ROW_NUMBER() OVER (PARTITION BY UniqueID ORDER BY TimeStamp DESC) AS SEQ FROM TransactionTable WHERE ActionPerformed LIKE 'action' AND TimeStamp > '1/1/2016' )PR WHERE SEQ = 1