Getting the first row

Moyeen Khan

Getting the first row
Hi List

I have two col1 col2 in a DB2 table.Col1 can repeat while col2 has unique
values. I would like to get only the first value for Col1 and col2 using
SELECT without opening/closing a CURSOR. Is it possible?

Thanks



Dave Nance

Re: Getting the first row
(in response to Moyeen Khan)
With V7, yes. Otherwise open/fetch/close.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/12/02 11:26:28 AM >>>
Hi List

I have two col1 col2 in a DB2 table.Col1 can repeat while col2 has unique
values. I would like to get only the first value for Col1 and col2 using
SELECT without opening/closing a CURSOR. Is it possible?

Thanks




This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

Agus Kwee

Re: Getting the first row
(in response to Dave Nance)
Moyeen,

In DB2 Version 7 for z/OS you can do it with:

SELECT ... INTO ... FETCH FIRST 1 ROW ONLY

Agus Kwee
Themis Training



Bernd Oppolzer

Re: Getting the first row
(in response to Agus Kwee)
this works, but there is still one problem:

if you have a key consisting of col1, col2, and you want a given col1 value,
together with the lowest value of col2 for this col1 value, you would need a
sort, as in

select col1, col2
into :col1, :col2
from t
where col1 = :col1
order by col2
fetch first 1 row only;

but this does not work, because into is not allowed together with order,
because before V7, into meant one row, and order always meant multiple rows.

this seems to me to be a design error, this should work in my opinion.

ok, i could do something like

select col1, col2
into :col1, :col2
from t t1
where col1 = :col1
and col2 = (select min(col2)
from t
where col1 = t1.col1);

but i'd prefer the "fetch first 1 row solution".

any ideas ?

Regards

Bernd


Am Don, 12 Dez 2002 schrieben Sie:
> Moyeen,
>
> In DB2 Version 7 for z/OS you can do it with:
>
> SELECT ... INTO ... FETCH FIRST 1 ROW ONLY
>
> Agus Kwee
> Themis Training
>



Agus Kwee

Re: Getting the first row
(in response to Bernd Oppolzer)
Moyeen,

With DB2 Version 7 for z/OS you can do it with:
SELECT .... INTO .... FETCH FIRST 1 ROW ONLY

Agus Kwee
Themis Training



Terry Purcell

Re: Getting the first row
(in response to Agus Kwee)
Bernd,

You are right, the FETCH FIRST in a singleton select does not give you the
"first" row based upon any ordering, just the first row arbitrarily. This is
because the ORDER BY is not valid in a singleton select.

If you must have a singleton select, and not a cursor, then make sure your
subquery is non-correlated in your example:

select col2
into :col2
from t t1
where col1 = :col1
and col2 = (select min(col2)
from t
where col1 = :col1);

Remember too that if you are V5 or V6 (and V6 without APAR & ZPARM set) you
will need a COALESCE on the MIN function to ensure indexability.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Bernd Oppolzer
Sent: Thursday, December 12, 2002 4:04 PM
To: [login to unmask email]
Subject: Re: Getting the first row


this works, but there is still one problem:

if you have a key consisting of col1, col2, and you want a given col1 value,
together with the lowest value of col2 for this col1 value, you would need a
sort, as in

select col1, col2
into :col1, :col2
from t
where col1 = :col1
order by col2
fetch first 1 row only;

but this does not work, because into is not allowed together with order,
because before V7, into meant one row, and order always meant multiple rows.

this seems to me to be a design error, this should work in my opinion.

ok, i could do something like

select col1, col2
into :col1, :col2
from t t1
where col1 = :col1
and col2 = (select min(col2)
from t
where col1 = t1.col1);

but i'd prefer the "fetch first 1 row solution".

any ideas ?

Regards

Bernd


Am Don, 12 Dez 2002 schrieben Sie:
> Moyeen,
>
> In DB2 Version 7 for z/OS you can do it with:
>
> SELECT ... INTO ... FETCH FIRST 1 ROW ONLY
>
> Agus Kwee
> Themis Training
>