Max Value and Date it occured

[#1]

Hi!

Need some help to solve this problem.

I have a table with ID, Date and Value, as below and I want to show maxValue and Date it occure, for each ID.

ID      Date                           Value

123     2011-01-17 13:00:00     11

123     2011-01-16 18:30:00     10

145     2011-01-19 14:50:00     4

145     2011-01-19 18:00:00     6

165     2011-01-30 11:00:00     3

165     2011-02-01 20:15:00     6

165     2011-01-31 07:45:00     4

The result I want:

123     2011-01-17 13:00:00     11

145     2011-01-19 18:00:00     6

165     2011-02-01 20:15:00     6

Regards

Peter

[CELAMBARASAN · Accepted solution]

Hi,

     Try with this

     Load

          ID,

          FirstValue(Date) as MaxDate,

          FirstValue(Value) as MaxValue

     Resident TableName Group By ID;

Make sure that you loaded the table data with order by Value in desc order and ID in asc order.

Hope it helps

Celambarasan

[jagan]

Hi,

Please check the attached file for solution.  Hope it helps you.

Regards,

Jagan.

[#4]

Thanks to both of you for quick answers, it works perfect.

Regards

Peter