Using prepared statement without ROW_NUMBER() and

问题:

Let's say I have a table T_SWA.This is my prepared statement.

Select version 
From (Select id, version, creator, 
       created_date ROW_NUMBER() OVER(order by created_date) cnt 
    From T_SWA 
    Where cnt=3 and id=35); 

I need to select the 3rd recent version from the T_SWA table. Can anyone suggest a replacement for this query without using ROW_NUM() and OVER() functiions?

回答1:

First take the three most recent and then from those three take the first.

select id, version, creator, created_date 
from (
    select id, version, creator, created_date 
        from T_SWA
        where id = 35
        order by created_date desc
        fetch first 3 rows only
)
order by created_date
fetch first 1 row only;