MVSFORUMS.com

[sendeepr]

Hi

1. I need only the first 2 rows to be fetched from a table which is already sorted ?

2. I need the 997 row to be fetched from a sorted table of 1000 rows ?

[SureshKumar]

1. Are you refering to DB2 ? what do you mean by 'already sorted', i assume you have an order by clause. If using V7 - FETCH FIRST 2 ROWS ONLY.
2. I am not sure about this, why not reverse the sort order and pick the first three rows from top, discard the first two and you have the 997th row.

[kolusu]

sendeepr,

Good answers Suresh Kumar.

Fetch FIRST N Rows is a new feature of DB2 Version 7. so if your shop does not have DB2 version 7 then you can define a cursor with ORDER BY in the program and fetch it just once.

For the second question define the cursor with ORDER BY KEY DESC and fetch it 4 times and get the 997th record.

Hope this helps...

cheers

kolusu

[naren_ab]

Hi all, I agree with Kolusu and suresh as long as the data is not large, and
you don't have need for where clause and i think sendeepr has small data.

However i have similar problem, yet to find solution. My table is as large as 10 mi rows. we have ried this solution like month back and it costed us a lot on DB2. the problem is when you do order by desc it is doing tablespace scan. In case of order by asc it knows to follow the indexes.
examples

Select ID from tableA
where ID >= 0
fetch first 100 rows only
order by ID;

Select ID from tableA
where ID <= 99999999
fetch first 100 rows only
order by ID DESC; the only solution we have in hand is to know one more value for the key so that we can between and save db2 cost.

[SureshKumar]

Thanks Naren, that was a good catch. I should have cautioned Sandeepr about the table size. Hopefully, V8 has promised to takecare by allowing a backwards scan on an index. Thanks

[sendeepr]

hi all

thank u very much for the replies ...
Q1 is ok
Q2 i need only the 4th or 997th row alone to be retrieved....pls clarify...?

anyway happy for the response

[kolusu]

Sendeepr,

Did you read all the answers clearly?? The second question of yours is also answered.Please read the posts once again

Thanks

kolusu

[sendeepr]

ok i did

thankx

can u explain the limitations of using the LIKE in DB2 ....it is not that easy...

[kolusu]

sendeepr,

The limitations of LIKE predicate depends on whether it is indexable or not.

[a]. If you code COL LIKE 'pattern' then it is indexable except if a field procedure exists on that column.

. If you code [b]COL NOT LIKE ' char', then it is NOT indexable.

[c]. If you code COL LIKE '%char' , then it is NOT indexable.It is Indexable only if an ESCAPE character is specified and used in the LIKE predicate. For example, COL LIKE '+%char' ESCAPE '+' is indexable. This rule is not applicable if a field procedure exists on that column.

[d]. If you code COL LIKE '_char' same rule as C.
[e]. If you code COL LIKE host variable, then it is indexable only if the pattern in the host variable is an indexable constant (for example, host variable='char%'). This rule is not applicable if a field procedure exists on that column.

check this link for a detailed explanation of All you want to know about LIKE predicate

Hope this helps...

cheers

kolusu