CODEWITHSUNDEEP
sqloracle-database
SofaH3ro
SofaH3ro

Reputation: 23

Get latest table entry by a unique ID with multiple time stamps

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.

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.

enter image description here

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

Upvotes: 1

Views: 342

Answers (1)

jimmy8ball
jimmy8ball

Reputation: 756

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

Upvotes: 1

Related Questions