Select a record with most recent date and time in Oracle

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?

Combine Date and Time column and cast it into Datetime. Then select the top 1 order by descending order of this. — Ullas, Apr 18 '17 at 14:34
@Ullas Oracle does not have a `DATETIME` data type - the `DATE` data type has both a date and time component. Also, before Oracle 12c, the `TOP 1` syntax is not supported. — MT0, Apr 18 '17 at 14:45
@MT0 : Oh Sorry, I didn't know that. My mistake. I just commented based on my SQL Server knowledge — Ullas, Apr 19 '17 at 02:19

MT0 · Answer 1 · 2017-04-18T14:49:32.303

5

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;

edited Apr 18 '17 at 14:49

answered Apr 18 '17 at 14:42

MT0

86,097
7
42
90

I used the following query and seems like working : select * from TransactionRecords where (to_date(to_char(TransactionDate ,'dd-mon-yyyy') ||' '|| TransactionTime ,'dd-mon-yyyy hh24:mi:ss')) = (select max(to_date(to_char(TransactionDate ,'dd-mon-yyyy') ||' '|| TransactionTime ,'dd-mon-yyyy hh24:mi:ss')) from TransactionRecords) – Dhrubojyoti Bhattacharjee Apr 18 '17 at 15:35

score 1 · Answer 2 · answered Apr 18 '17 at 14:37

1

Use row_number(). I've used a CTE for visibility, but subquery will work too

with CTE as
(
  select t1.*,
         row_number() over(order by TransactionDate desc, TransactionTime desc) as rn
  from MyTable t1
)
select CTE.*
from CTE
where rn = 1

answered Apr 18 '17 at 14:37

JohnHC

10,417
1
19
36

score -2 · Answer 3 · answered Apr 18 '17 at 14:44

-2

select * from TransactionRecords tst ORDER BY tst.TransactionDate||tst.TransactionTime DESC

answered Apr 18 '17 at 14:44

Tigran Shahnazaryan

130
2
9

Using `||` string concatenation on a `DATE` data type will implicitly call `TO_CHAR()` on the transaction date using the `NLS_DATE_FORMAT` session parameter as the format mask - unless this is set to an ISO8601 type format, this is likely not to lead to the correct ordering (and can be broken by individual users changing their session parameters without changing the query). – MT0 Apr 18 '17 at 14:56

Select a record with most recent date and time in Oracle

3 Answers3