通过带有多个时间戳的唯一ID获取最新的表条目 [英] Get latest table entry by a unique ID with multiple time stamps

问题描述

我在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

这篇关于通过带有多个时间戳的唯一ID获取最新的表条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！