Group by MAX

[roy999]

Hi....I need some help again.

I've got a table with tel no's. In this table are fields called tel_obj,cust_id and the tel details. Tel_obj increases when tel no's are updated. So 1 customer could end up with 5 records in the table, of which only the last record is significant. I tried using FIND LAST, but the results are not that accurate. In access I group it by the MAX tel_obj, the assign the MAX tel_obj to the relevant cust_id record in the temp table. Any ideas on how to do this in progress??

 

[parul]

This is because when you say find last progress uses an index to find the record.
What are the index on this table? I think you are not using the correct index to get to the record (This is partly the reason why FIND LAST is discouraged).

You can create an index tel_obj and then if you say find last..... use-index tel_obj. You will get the last record you want.

You will not find Use-index and find last in best practice documents though but in this case i think the use is OK (others will point out if not).

-Parul.

 

[roy999]

Where can i find the best practice documents?

 

[parul]

Sorry my mistake... there is no "document" (as far as I am aware) as such apart from the last chapter of mordern progress (very basic).

You can find these things in the presentations on PSDN and discussions on PEG, progresstalk.

There are few things like shared-lock, can-do, use-index, find first/last etc that have there uses but are not encouraged by the community.

Simply because they take away more than they add.

-Parul.

 

[tamhas]

See answer here http://www.psdn.com/library/thread.jspa?threadID=7607&tstart=0

 

[Casper]

If only the last one is significant then why not delete the other 4

What is the unique key in the table?

If you had an index like cust-id tel_obj then you could do a find last with a consistent result.
Otherwise for last where cust-id = ... by tel_obj
would give a consistent result as well. It would be nice to know the indexes on the table though....

Casper.