0

I have a column called "SYS_CREAT_TS". I want the query to fetch the REV data where Status code is 2 and from the latest timestamp where status code is 2. 

 SELECT RVSN FROM DATA_STUS WHERE DATA_STUS_CD = 2 AND SYS_CREAT_TS IN MAX(SYS_CREAT_TS);

Some more detail

Without the latest timestamp comparison query. I'm getting Revision Number(RVSN) as 2446, 2442.

But I want the latest timestamp between these two timestamps with their respective revision numbers.

  1. 15-JUL-15 03.20.25.769000000 PM -> 2442
  2. 15-JUL-15 03.23.03.940000000 PM -> 2446

The second one is the latest. Im using Oracle 12C. So, the result of the query should be 2446.

AppSensei
  • 8,270
  • 22
  • 71
  • 99
  • Your question needs more detail. What is your table's schema? Why can't you run two subsequent queries? What does `RVSN` mean? And why are your column-names in uppercase? – Dai Jul 15 '15 at 20:00
  • 3
    And which RDBMS are you using? – potashin Jul 15 '15 at 20:00

3 Answers3

1

This will work:

select rvsn
from (
  select rvsn, row_number() over (order by sys_creat_ts desc) as rn
  from data_stus
  where data_stus_cd = 2
) where rn = 1;
sstan
  • 35,425
  • 6
  • 48
  • 66
0
with x as (select *, row_number() over(order by SYS_CREAT_TS desc) as rn 
from DATA_STUS)
SELECT RVSN FROM x 
WHERE rn =1 and DATA_STUS_CD = 2

If the data needs partitioning by a column, add it to the over clause so you get the desired results.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
0
SELECT RVSN 
FROM DATA_STUS 
WHERE DATA_STUS_CD = 2 
AND SYS_CREAT_TS  = (SELECT MAX(SYS_CREAT_TS)
                     FROM RVSN
                     WHERE DATA_STUS_CD = 2)

Would something like this work for your issue?

Taku_
  • 1,505
  • 3
  • 14
  • 22