Help with 'group by' query

[donbrown44]

I would like to create a query that 'groups by' the first two columns, selects the 'max' of the third column, then 'carries over' the information in the last two columns that go along with the same row as 'max' of the third column. See below example input and desired output:

Input Table
C1, C2, C3, C4, C5
Mike, Smith, 10, D1, D2
Mike, Jones, 50, D1, D2
Joe, Smith, 60, D1, D2
Joe, Brown, 10, K1, D2
Mike, Smith, 20, D1, D2
Mike, Smith, 30, K1, K2
Mike, Jones, 60, K1, K2
Mike, Jones, 40, D1, D2


Desired output table

Mike, Smith, 30, K1, K2
Mike, Jones, 60, K1, K2
Joe, Smith, 60, D1, D2
Joe, Brown, 10, K1, D2

I've figured out how to 'group by' the first two columns and get the 'max' of the third column. I cannot figure out how to 'carry over' the last two columns. Any help would be appreciated... Thanks!

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/LastValue.htm

[donbrown44]

Ahah... two queries - didn't even think of that. Or maybe I was just concentrating too hard on doing everything in one query.

Thanks for the reply - I'll try the two queries approach and mark this as solved if I get it working.

[pbaldy]

No problem. It can be done in one query, with the first as a subquery, but I find it easier to work with them separately.