SIMPLE ?? Solution

[20leafclover]

Can you help

In this example:

ABC DEF
Num Num Note

101 101 test1
test2
test3

102 102 test1
test2
test3

(1)How can I retrieve ONLY the LAST NOTE for Each Num field.
I want to retireve ONLY: 101 - test3, 102 - test3

(2) What is the SQL equivilant statement to do this same thing ?

 

[carolynh]

20leafclover
Try the FIND LAST function.

LAST
Uses the criteria in the record-phrase to find the last record in the table that meets that criteria. Progress finds the last record before sorting.

 

[carolynh]

Sorry I hit the submit button prematurely.

You can also use the LAST-OF fuction in an IF statement.

 

[richtpt]

Um, this assumes you have an index on both fields. If you don't have an index on DEF (test3), you might not get test3 when you do a last. This issue has come up big time on the PEG.

Basically, if you want to see if there's a record in that table, find first/last is great. If you want a specific record, you may or may not get that record with first/last. One way to always get that record is to do a FOR FIRST or FOR LAST with a BY phrase that would include the fields you want. Something like

FOR LAST table NO-LOCK
BY ABC BY DEF:

END.

This will always make sure you get the test3 record you want. Alternatively, check the indexes you have. If you have an index that's ABC/DEF on that table, then your FIND LAST should be fine - do a COMPILE XREF to double-check which indexes are being used.

Hope that helps!

Rich

 

[Faelcu]

If your file doesn't have the indices you need you can load a temp-table and build the needed index there. Then you can use the "if last-of(temp-table.note)".