0

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?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
vijayakumarpsg587
  • 1,079
  • 3
  • 22
  • 39
  • What is the problem with using ROW_NUMBER to achieve your result? What are you trying to do differently? – Ian Bjorhovde Sep 30 '10 at 09:56
  • @Ian: The clients of our company are not allowing us to use it with OVER() and ROW_NUMBER() functions..That's why we are looking for alternative solutions. So do u have any?? – vijayakumarpsg587 Sep 30 '10 at 10:24

1 Answers1

0

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;
boes
  • 2,835
  • 2
  • 23
  • 28
  • When i tried to find the nth record , a datatype mismatch occurs, possibly at the place where the fetch keyword is being used.Can the Fetch keyword be used to find the nth record?? – vijayakumarpsg587 Sep 30 '10 at 13:07
  • No. See http://stackoverflow.com/questions/3751884/paramertize-fetch-first-n-rows-only-in-db2/3774458#3774458 and then the second comment. – boes Sep 30 '10 at 17:07
  • WARNING?: I do not agree. Maybe there is some misunderstanding about what the requested '3rd recent version' does mean. Let's assume there is a version for every day of september 2010. There will be 30 records from 1 till 30 september in the table. The '3rd recent version' would be the version of 28 september. The inner select, ordered by created_date desc, will return september 30, 29 and 28. The outer select, ordered by created_date, will return september 28. Am I missing something? – boes Oct 01 '10 at 07:41