By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

column functions

P: n/a
Hi all,

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

Regards,

tuarek

Apr 11 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Apr 11 '06 #2

P: n/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.
Apr 11 '06 #3

P: n/a
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

Apr 11 '06 #4

P: n/a
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
Apr 11 '06 #5

P: n/a
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

Apr 12 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.