Select a record with most recent date and time in Oracle [duplicate]

Question

I have a question regarding a SQl query in Oracle where IO need to select rthe row with the most recent date and time where date and time are inserted into two separate columns.

Table TransactionRecords

 CustomerID    TransactionDate   TransactionTime
-------------------------------------------------------
  0206016         17-APR-17         15:29:34
  0213570         17-APR-17         15:29:32
  0211384         17-APR-17         13:29:34
  0487674         16-APR-17         14:29:30
  0487759         15-APR-17         13:29:32
  0487213         12-APR-17         13:29:32
  0191022         11-APR-17         15:29:33
  1141158         29-OCT-16         01:25:51

I am expecting my query to rerturn the most recent transaction which should be :

   CustomerID    TransactionDate   TransactionTime
  ---------------------------------------------------
    0206016        17-APR-17            15:29:34

When I am running the following query :

 select * from TransactionRecords tst
 where tst.TransactionDate  in (select max(TransactionDate) from 
 TransactionRecords)

I am getting all the transactions that happened on the most recent date. Can someone please give me any suggestions?

Answer 1

In Oracle 12c you can do:

SELECT *
FROM   your_table
ORDER BY TransactionDate DESC, TransactionTime DESC
FETCH  FIRST 1 ROWS ONLY;

In lower versions, you can use the ROWNUM pseudo-column:

SELECT *
FROM   (
  SELECT *
  FROM   your_table
  ORDER BY TransactionDate DESC, TransactionTime DESC
)
WHERE  ROWNUM = 1;