ROWNUM functionality DB2 Ver 5

[rajuaravind2003]

Hi Everbody

i am using db2 version 5. i have a requirement of querying based on row numbers.

for instance i might need to write a query, which looks like this

select * from employee_table fetch rows between 30 and 50

can anyone help me on how to achieve this in db2 ver 5

[docdiesel]

Hi,

I don't know if the function row_number() is supported in DB2 v5 (I suspect it's not but hope I'm wrong), but you could try the following:

Select
  ROW_NUMBER() OVER(order by some_field asc) as ROWNO,
  some,other,fields
From
  employee_table
WHERE
  ROWNO>30
ANND
  ROWNO<=50
;

Please let us know if DB2 v5 is able to work like this.

Regards,

Bernd

[rajuaravind2003]

Hi Bernd

Thanks for you suggestion. But unfortunately for us, V5.0 doesnt support ROW_NUMBER() :(

Any more suggestions will be greatly appreciated.

Thanks

Raju

Hi,

I don't know if the function row_number() is supported in DB2 v5 (I suspect it's not but hope I'm wrong), but you could try the following:

Select
  ROW_NUMBER() OVER(order by some_field asc) as ROWNO,
  some,other,fields
From
  employee_table
WHERE
  ROWNO>30
ANND
  ROWNO<=50
;

Please let us know if DB2 v5 is able to work like this.

Regards,

Bernd

[docdiesel]

Hi,

hm, then the only thing that's left to help you is FETCH FIRST x ROWS ONLY. But for that you'll need some order criteria, like an employee id, which I call "e_id" for the following example. You'll have to

• select all rows e_id, ordered ascending by e_id
• fetch the first 30 rows of them
• and get the maximum value of e_id (means row 30)
• then do the same for the 50th row
• and finally use these ids to select * the appropriate rows

The code will look like this monster sql code:

select
  *
from
  employee
where
-- e_id>30
  e_id>(
    select
      max(tmptbl1.e_id)
    from
      (
        select
          e_id
        from
          employee
        order by
          e_id asc
        fetch first 30 rows only
      ) tmptbl1
  )
 
and
-- e_id<=50
  e_id<=(
    select
      max(tmptbl2.e_id)
    from
      (
        select
          e_id
        from
          employee
        order by
          e_id asc
        fetch first 50 rows only
      ) tmptbl2
  )
 
order by e_id asc
;
 

and maybe it's awfully slow if your table is more than 10000 rows, but it's working on my test table.

Let me know if this fits to the needs of a v5 DB2.

Regards,

Bernd