Choosing field from same row based on an aggregate.

I have a table with two columns. Let's call them Value and Hour. Looks like this:

Value Hour

4 9:00

3 11:00

6 2:00

2 12:00

I want to be able to make a total line and give the Max(Value) and the time it happened. What would be the function to get the Hour value based on the Max(Value). Just for example, for this one it would be Max(Value) = 6 and it's Hour would be 2:00.

Hi,

what about getting this right back from the database system with a query ?

SELECT Hour,MAX(Value)
From SomeTable
Group by Hour

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I'm actually getting my data from a Custom Data Processing extension. I would need a way to get it with a function straight out of the resultant dataset.|||

Can't you make a calculated field with the expression = Max(Value)?

That can be put in the footer of the table (which will display '6' in this example). To get the hour belonging to the value with an expression ...I'm not so sure how to do that ... maybe with a switch expression, based on the textbox where you show the MAX(Value)?