Select the maximum variation including deleted records

The highest variation of a record is returned, even if the highest variation record status is deleted. This logic is useful when the query joins tables, and a record may be deleted in one table but referenced in another.

There are two methods to select the maximum variation of each record and include deleted records for an object. You can use an Infor table function or a query hint.

The query results are the same if you use a variation handling hint or Infor variation handling table function.

Selecting the maximum variation of each record and including records in which the maximum variation is deleted returns the results as shown in this table:

Company IdentifierPath	ProductID IdentifierPath	Description	Price	Variation VariationPath	DeletedFlag DeleteIndicator	ArchivedFlag ArchiveIndicator
001	991041	Bike	350	2	true	false
003	12345	Skateboard	205	3	false	false

Notice that lower record variations are excluded from the query results.

The results include highest variation of each record, regardless of the deleted status. The results include variation 2 of the record for company 001, productID 991041, and variation 3, of the record for company 003, productID 12345. The record variation 2 for company 002 productID 222333 was not included because it is archived.