467,189 Members | 1,348 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

column functions

Hi all,

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

Regards,

tuarek

Apr 11 '06 #1
  • viewed: 3838
Share:
5 Replies
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
"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
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
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
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.

Similar topics

5 posts views Thread by Demetris | last post: by
4 posts views Thread by Peter Scott | last post: by
11 posts views Thread by csomberg@dwr.com | last post: by
2 posts views Thread by Irfan Bondre | last post: by
1 post views Thread by neelu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.