CODEWITHSUNDEEP
progress-4glopenedge
Matt_Johndon
Matt_Johndon

Reputation: 204

FOR LAST - Query, giving wrong result

I'm looking to use the following query to find the last tender id. 

FOR EACH tender-table NO-LOCK WHERE tender-table.kco = 1 BY tender-table.id:
DISPLAY  tender-table.id.
END.

This query looks at all the tender id's and brings back all the results of all the id's in ascending order. The results i get are

1,035 1.036 ...... 1,060 1,061 1,062 1,063 1,064 1,065 1,066

FOR LAST tender-table NO-LOCK WHERE tender-table.kco = 1 BY tender-table.id:
DISPLAY  tender-table.id.
END.

However when i use this query to find the last id, i get the result,

1,061

When I should be seeing the result 1,066. Can anyone suggest why this is happening?

Upvotes: 3

Views: 5306

Answers (2)

Tom Bascom
Tom Bascom

Reputation: 14020

FOR LAST is a very deceptive statement. (So is FOR FIRST.) It does not behave in an intuitive manner. The sort order is NOT specified by the BY statement. You will get the LAST record according to the index which is used and no sorting will take place. When the BY refers to an unindexed field (or one which does not sort in the order of the index actually used) or when the WHERE clause does not obviously map to an index in the order that you are hoping for you will have mysterious records chosen.

Personally, I strongly suggest that you forget about using FOR FIRST & FOR LAST. A better option, which always sorts as expected, would be:

FOR EACH tableName WHERE someCriteria BREAK BY sortOrder:
  LEAVE.
END.
DISPLAY whatEver.

(Add "DESCENDING" to flip from FIRST to LAST...)

Just in case anyone needs convincing -- try this with the "sports" database:

for first customer no-lock by discount:
  display name discount.
end.

Upvotes: 7

Matt_Johndon
Matt_Johndon

Reputation: 204

Sorry I have managed to figure it out that the 1,066 values didn't have tender-table.kco = 1. this solves the problem. thanks your time.

Upvotes: 0

Related Questions