column functions

[tuarek]

Hi all,

Do you know of any udb column functions resembling MS-Access "First" /
"Last" functions?

Regards,

tuarek

[Serge Rielau]

tuarek wrote:

Hi all,

Do you know of any udb column functions resembling MS-Access "First" /
"Last" functions?

There are misc tricks.
If all you want is the MAX or MIN row use:
SELECT * FROM T ORDER BY X [ASC|DESC] FETCH FIRST ROW ONLY;
If you need the last/first per group you can use:

SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY group ORDER BY sort) AS rn, T.*
FROM T) AS X WHERE rn = 1;

If you need LAST and FIRST in the same row things are getting
progressively messier.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Mark A]

"tuarek" <mb*******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

Hi all,

Do you know of any udb column functions resembling MS-Access "First" /
"Last" functions?

Regards,

tuarek

You will get more responses if you explain what they do.

[tuarek]

Thanks Serge...

Mark:

First & Last functions are aggregate functions like Min,MAX and SUM.
They return the first/last values from the result set returned by the
query.

For more information you can check:
http://office.microsoft.com/en-us/as...499571033.aspx

Regards,

Mehmet

[Knut Stolze]

tuarek wrote:

Thanks Serge...

Mark:

First & Last functions are aggregate functions like Min,MAX and SUM.
They return the first/last values from the result set returned by the
query.

The thing is that SQL is a set-oriented languages and the elements in a set
have - per definition - no order. So the terms "first" and "last" row are
completely meaningless, unless you impose a specific order in which the
rows are to be retrieved. And once you have such an order, the FETCH FIRST
n ROWS clause that Serge mentioned, should give you exactly what you want.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Tonkuma]

WITH
Your_Query AS (
-- write your query
ORDER BY X, Y, Z
/* Depending on Remarks in your referenced site. without ORDER BY "....
the records returned by these functions will be arbitrary." So, it
would be better to specify ORDER BY */
)
SELECT Y.*
, (SELECTexpr FROM Your_Query ORDER BY X, Y, Z FETCH FIRST 1 ROWS
ONLY)
FROM Your_Query Y