Max Of A Specific Field In A Table

[JM3482]

I am trying to code to pull the most updated address for our members in a member address table, which consist of over 2 million records. Each member will have multiple records in this table. I need the most updated address. The fields available are of course street, city, state, zip but there is also a field called addr_type_indic with data such as R for residential, M for mailing, there is a B and O but I don't know what they are for. There is also a date/time stamp field called Time_stamp. How would I code to pull the max of the Addr_type_indic field and group by the 01 member (mem_no field) (or the main member identification number).

I was told to use the max statement but don't know if I would use this in my field selection or the where clause.

Please Help!!!!

 

[jymm]

is your DB a SQL database???

If so I would do it this way to get the most recent address update for the member --- ymmv:

Select Mem_no_field, Street, City, State, zip, addr_type_inc, max(time_stamp)
from address_db
group by mem_no_field

I am not quite sure what you meant by 'max of the addr_type_indic'.... the max of that field (since it is R, M, B, O) would be R for most places... Are you looking for the most recent member update OR the most recent member AND type update --- in which case add the addr_type_inc to the group by line

If you have to put this into SAS, just put the above code into a proc sql step... This help?

 

[jymm]

doh - a dear... missed one small thing since I did not actually run this through query analyzer and killed off sas a while back...

Select Mem_no_field, Street, City, State, zip, addr_type_inc, max(time_stamp) as last_date
from address_db
group by mem_no_field

forgot the 'as last_date' so that things would come back right (sigh - more caffine... need it now)

 

[JM3482]

Thanks so much for the information. Yes I would assume it was as SQL database. It is housed in California on a server. In answer to your question about the most recent update, I am looking for the most recent address for the 01 member, but we need to make sure that I pull the most recent addr_type_indic that goes with the address also.

I need caffine also. I will try your last statement with the last_date on max time stamp.

Thanks and have a good one.

 

[jymm]

let me know if we need to tweak it a bit --- that code should get you a good start on things.