Query Select Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

matias.cornejo@gmail.com - 14 May 2007 23:56 GMT

I have a table with 1.000.000 registers and I want to find the
register in a position 25.000. I don't want to use a cursor, how can I
do it in other way?

Thenks in advance

Reply to this Message

Lennart - 15 May 2007 05:21 GMT

> I have a table with 1.000.000 registers and I want to find the
> register in a position 25.000. I don't want to use a cursor, how can I
> do it in other way?
>
> Thenks in advance

select * from (
select T.*, row_number() over (order by <position>) as rn
from T
) X where rn = 25000

/Lennart

Reply to this Message

Knut Stolze - 15 May 2007 11:02 GMT

> I have a table with 1.000.000 registers and I want to find the
> register in a position 25.000. I don't want to use a cursor, how can I
> do it in other way?

Tables contain sets of rows. Sets are - per definition - not ordered. So
there is no 1st, 2nd, or 25000th row. So you have to define a sort
criteria and then you can use Lennart's query, for example.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

matias.cornejo@gmail.com - 16 May 2007 16:28 GMT

> matias.corn...@gmail.com wrote:
> > I have a table with 1.000.000 registers and I want to find the
[quoted text clipped - 9 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Thaks to all.

I did the next query:

select rut_cliente FROM (SELECT ROW_NUMBER() OVER (ORDER BY
RUT_CLIENTE) AS POSITION, RUT_CLIENTE FROM TABLE_NAME ORDER BY
RUT_CLIENTE) AS x WHERE POSITION = 2

Reply to this Message

Serge Rielau - 16 May 2007 16:44 GMT

> select rut_cliente FROM (SELECT ROW_NUMBER() OVER (ORDER BY
> RUT_CLIENTE) AS POSITION, RUT_CLIENTE FROM TABLE_NAME ORDER BY
> RUT_CLIENTE) AS x WHERE POSITION = 2

FYI: You don't need the ORDER BY on the query itself. The one in
ROW_NUMBER() OVER() is sufficient.
Won't change the plan though. Just less typing

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message