I have one Table. This Table has multiple identical ID#s (Rows/Records).
Each ID Record/Row has an Effective Sequence field/column(Integer) and a
EffectiveDate field/column (YYMD). [Same ID, Different Column/Field Values)

I want to filter/select/return only the row/record for an ID
with the Max/Highest Effective Sequence number(Integer) column and
Max/Highest EffDate (Date) column for that ID (record).

The Report Result/Output is only 1/unique Row/Record (ID Column) value with the
highest highest value for EffSequence and EffDate.

Here is the Output/Report Column Format:

ID Eff.Sequence Eff.Date TypeCount

Any assistance will be appreciated.

Thanks

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

Try this:

TABLE FILE <tablename>
SUM MAX.EffSequence
    MAX.EffDate
    CNT.ID AS 'TypeCount'
 BY ID
END

Dan,

Your response was very helpful. It answered my question on filtering ID BY MAX.FLD in a Record.

I didn't want to close this question out immediately. Since I knew, I'd have a very similiar related question.

I am doing a GENDER COUNT as an ACROSS FIELD.

However there are duplicate-mulitiple IDs in the BY (Record).

How do I filter out duplicate IDs or just COUNT 1 ID (MULITPLE RECORDS w SAME ID)?

TABLE FILE STDNT_ENRL
SUM
'CNT.STDNT_ENRL.EMPLID'
ACROSS 'J0.PERSONAL_DATA.SEX'
HEADING
""
FOOTING
""
WHERE (STDNT_ENRL.INSTITUTION EQ 'INSTI' ) AND ( STDNT_ENRL.STRM EQ '1108' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON

This is Current HTML OUTPUT

Sex
F M U

--------------------------------------------------------------------------------

3035 1867 6

The PRINT values count/contains multiple IDS

Any help would be appreciated.

Thanks

Mike

This may require a two step solution:

TABLEF FILE STDNT_ENRL
 COUNT DST.STDNT_ENRL.EMPLID
 BY J0.PERSONAL_DATA.SEX
 WHERE (STDNT_ENRL.INSTITUTION EQ 'INSTI') AND ( STDNT_ENRL.STRM EQ '1108');
 ON TABLE HOLD
END
-*
TABLE FILE HOLD
 SUM EMPLID
 ACROSS SEX
 HEADING
  ""
 FOOTING
  ""
 ON TABLE SET PAGE-NUM OFF 
 ON TABLE NOTOTAL
 ON TABLE PCHOLD FORMAT HTML
 ON TABLE SET HTMLCSS ON 
 .
 .
 .
END

Dan,

You can close this Question & Thread Out.

It was helpful. The first part, particulalry.

Thanks

Mike

Mike, actually it is you who can close it . Just edit your first post and edit the title prefixing it with [CLOSED].