Using WebFocus Developer Studio 7.7.03 - Excel 2007 output

I am working within one table (no joins) and I am receiving duplicate records by account number because there are multiple steps completed and captured. How do I return only one record per account number, showing only the maximum or using the LAST function so I can sort the completions in order and say if the last account isn't a duplicate, define as keep, else dup.

Example of my Current Output:
Account# Sequence Step Step Comp Date
20000000 1 Z01 10/01/2012
20000000 2 Z03 10/15/2012
20000000 3 Z04 10/15/2012
55500000 1 Z01 03/01/2011
55500000 2 Z03 03/05/2011
37000000 1 Z01 10/01/2012
37000000 2 Z03 06/05/2012
37000000 3 Z04 07/12/2012
37000000 4 Z13 07/27/2012
99999998 1 Z01 08/05/2011
99999998 2 Z03 08/20/2011
99999998 3 Z04 09/03/2011

The sequence number is really the key. The step name is not sequencial, they jump around a bit. Like in account #2, it's possible for 2 steps to be completed on the same day, but the sequencer determines which supercedes.

I need it to look like this:

Account# Sequence Step Step Comp Date
20000000 3 Z04 10/15/2012
55500000 2 Z03 03/05/2011
37000000 4 Z13 07/27/2012
99999998 3 Z04 09/03/2011

I can't seem to get the MAX function to work, so now I am thinking it might be the LAST function and something I need to define.

Can someone please help?

Thank you!

This message has been edited. Last edited by: reportmasterlvl3,

SUM
LST.SEQUENCE
LST.COMP_DATE
BY ACCOUNT

BTW, we have over 900 users and all utilize InfoAssist just fine. It is an excellent tool for decentralizing simple reporting...

Thanks Tom! That seemed to do the trick. I'm not a coder, and I've never looked at the source before, so I just threw it in there and it worked. Thanks again for the quick response.

Although you have a way, another is to do a BY HIGHEST 1 on SEQUENCE and as long as there are no duplicate Sequence numbers it should work the same with PRINT or SUM.

SUM
STEP_STEP
COMP_DATE
BY ACCOUNT
BY HIGHEST 1 SEQUENCE

Thanks for your contribution, Crymsyn! There are no duplicate sequence numbers, so this method works as well, and is actually more simple and my query runs faster. Awesome! Thanks again.

This message has been edited. Last edited by: reportmasterlvl3,