corresponding attribute related to max record

[#1]

Hello all,

Below is my daily table. From this daily table, I only want to extract the max record based on recnum and all the corresponding data related to the max record into separate table. How do i go about it? Any help will be appreciated.

DAILY TABLE
DATE	recnum	STATUS	ID	STATUS_BHANGE
11/3/2010	199701	A	1	A TO B
11/3/2010	199702	B	1	A TO B
11/3/2010	200711	B	1	B TO A
11/3/2010	200712	A	1	B TO A
11/23/2010	1554971	A	1	A TO B
11/23/2010	1554972	B	1	A TO B
11/23/2010	1563321	B	1	B TO A
11/23/2010	1563322	A	1	B TO A

MAX TABLE
DATE	recnum	STATUS	ID	STATUS_BHANGE
11/23/2010	1563322	A	1	B TO A

[#2]

Hi there

I would use max(num(left(recnum,6)))

This way you only take out the number (if the recnum always starts with 6 digits, like if it is year/month) and parses it to a number which the max function can handle.

//John

[#3]

Hi John,

Sorry, the table did not post cleanly. I am not looking to parse the record.

Here is the simple explanation

max(recnum) group by ID will return recnum='1563322'

My max table should contain all attribute related to recnum 1563322

Hope this explains.

Thank you.

Pranita

[Anonymous]

Hi Pranita,

You could sort the table by recnum, descending, and then just do a resident load and load the first row only from the sorted table.