how to select the last row of the resultset

[xixi]

hi, we are running db2 udb on v8.1, i am using jdbc sql, is there
anyway to create a sql saying i want to get the last record of
resultset? i try to open a big resultset and point the cursor to the
last (like select * from table, then do resultset.last) , i only want
to get a one row back which is the last row of the resultset. thanks

[Serge Rielau]

Re: how to select the last row of the resultset

Can you simply produce the last row only in teh first place?
That would be the best.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Blair Adamache]

Re: how to select the last row of the resultset

To get the first row:

select * from address order by name asc fetch first 1 row only

To get the last row, order by the same column in reverse (descending)
order:

select * from address order by name desc fetch first 1 row only

xixi wrote:
[color=blue]
> hi, we are running db2 udb on v8.1, i am using jdbc sql, is there
> anyway to create a sql saying i want to get the last record of
> resultset? i try to open a big resultset and point the cursor to the
> last (like select * from table, then do resultset.last) , i only want
> to get a one row back which is the last row of the resultset. thanks[/color]

[PM \(pm3iinc-nospam\) CGO]

Re: how to select the last row of the resultset

What about you
.... order by x descending fetch first 1 rows for read only?

PM

"xixi" <dai_xi@yahoo.c om> a écrit dans le message de
news:c0f33a17.0 405260756.1bdd0 e37@posting.goo gle.com...[color=blue]
> hi, we are running db2 udb on v8.1, i am using jdbc sql, is there
> anyway to create a sql saying i want to get the last record of
> resultset? i try to open a big resultset and point the cursor to the
> last (like select * from table, then do resultset.last) , i only want
> to get a one row back which is the last row of the resultset. thanks[/color]

[Jean-David Beyer]

Re: how to select the last row of the resultset

xixi wrote:[color=blue]
> hi, we are running db2 udb on v8.1, i am using jdbc sql, is there
> anyway to create a sql saying i want to get the last record of
> resultset? i try to open a big resultset and point the cursor to the
> last (like select * from table, then do resultset.last) , i only want
> to get a one row back which is the last row of the resultset. thanks[/color]

I have always been confused by people wanting to do things like this,
because DB2 uses the relational model wherein the data are a set, not an
ordered set. So the idea of a "last row" is meaningless. So it escapes me
why people go out of their way to impose an ordering without defining the
ordering.

As a practical matter, it seems that DB2 would return rows in response to
a query (that does not have ORDER BY) in about the order they were entered
(if no reorganization has taken place) or in the order in which they were
presently stored. But it does not matter much which, as far as I can tell.

I am not saying it is not a possible solution to some problem or other.
But it seems to me that since the concept of "last row" is at best
undefined, it would seem to make more sense to define what is wanted and
retrieve that.

So if you want a "last row", why not do an ORDER BY ... and arrange for
the "last row" to come out first. You could even have the query contain
stuff like:

ORDER BY column1 DESC, column2 ASC ...
FOR READ ONLY
OPTIMIZE FOR 1 ROWS;

or something like that. If there is no such column or columns, put one in,
if only temporarily.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 09:15:00 up 4 days, 16:30, 3 users, load average: 3.47, 3.94, 4.03

[Blair Adamache]

Re: how to select the last row of the resultset

It does not matter, and it is not dependable - if you have no order by,
the result set is arbitrary. Rows stored on different devices, on
different machines with different CPU speeds, rows that have been
re-org'd - all of these things make order unpredictable without an ORDER
BY.

FWIW, it seems to be the java crowd presenting rows on web pages who
care most about adding arbitrary row numbers.

Jean-David Beyer wrote:
[color=blue]
>...
>
> As a practical matter, it seems that DB2 would return rows in response
> to a query (that does not have ORDER BY) in about the order they were
> entered (if no reorganization has taken place) or in the order in which
> they were presently stored. But it does not matter much which, as far as
> I can tell.[/color]