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

Using functions in select statment

P: n/a
Hi

I have a table with two columns: number and timestamp.

I would like to do a max function on number and also select the the
corresponding timestamp.

I expected the SQL to be something like this but doesnt work:

select timestamp, max(number) from table

How do I perform a SQL select that just returns the max value with the
corresponding timestamp value?

Cheers
Mahesh

Feb 2 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
select timestamp,number from table where number = (select max(number)
from table)

-Philip

Mahesh S wrote:
Hi

I have a table with two columns: number and timestamp.

I would like to do a max function on number and also select the the
corresponding timestamp.

I expected the SQL to be something like this but doesnt work:

select timestamp, max(number) from table

How do I perform a SQL select that just returns the max value with the
corresponding timestamp value?

Cheers
Mahesh
Feb 2 '07 #2

P: n/a
Mahesh S wrote:
I would like to do a max function on number and also select the the
corresponding timestamp.
SELECT timestamp FROM table ORDER BY number DESC FETCH 1 ROW ONLY

If you need to do it for multiple groups try this:
SELECT timestamp FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY mygroup ORDER BY number DESC)
AS rn, timestamp FROM table) AS X
WHERE rn = 1;

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 2 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.