In DFSORT there is no separate MIN or MAX aggregate in the SUM statement. Instead, you get the record with the minimum or maximum value in a field by combining sort order and SUM FIELDS=NONE. You sort by the group key and then by the value field—ascending for minimum (smallest first) or descending for maximum (largest first). The record you want is then the first in each group; SUM FIELDS=NONE keeps one record per key, which is that first record. This page explains how to achieve MIN and MAX per group, why sort order matters, how to handle dates and numeric formats, and the role of OPTION EQUALS.
The SUM statement in DFSORT supports SUM FIELDS=NONE (keep one record per key, no summing) and SUM FIELDS=(position, length, format,...) (sum numeric fields per key). It does not support a separate MIN or MAX function per field. So to get “the record with the maximum amount per customer” or “the record with the minimum date per department,” you use the fact that SUM FIELDS=NONE keeps the first record of each key group. You control which record is “first” by how you sort.
To keep the record that has the largest value in a given field for each key: sort by the key first, then by that value field in descending order. After the sort, within each key group the record with the maximum value is first. SUM FIELDS=NONE then keeps one record per key—that first record—so you get the record with the maximum value.
Input: fixed-length records with customer ID at 1–8 and a 4-byte packed decimal amount at 21–24. Requirement: one record per customer, and that record must be the one with the highest amount for that customer.
123OPTION EQUALS SORT FIELDS=(1,8,CH,A,21,4,PD,D) SUM FIELDS=NONE
Records are sorted by customer (1–8) and then by amount (21–24) descending. So for each customer, the record with the largest amount is first. SUM FIELDS=NONE keeps one record per customer—the first—which is the one with the maximum amount. OPTION EQUALS keeps the order of records with equal keys stable so the “first” is well-defined.
To keep the record that has the smallest value: sort by the key, then by the value field in ascending order. The record with the minimum value is first in each group; SUM FIELDS=NONE keeps that record.
Product ID at 1–6, quantity (2-byte binary) at 10–11. One record per product with the smallest quantity.
123OPTION EQUALS SORT FIELDS=(1,6,CH,A,10,2,BI,A) SUM FIELDS=NONE
Sorted by product and then by quantity ascending. The record with the minimum quantity is first per product; SUM keeps that one.
The same idea applies to dates. Use the date field as the secondary sort key. Ascending order puts the earliest date first (minimum date); descending puts the latest date first (maximum date). The date must be in a sortable format—e.g. YYYYMMDD as ZD or PD, or a character format that sorts chronologically (e.g. 20250101). If the date is in a format that does not sort correctly (e.g. DD/MM/YYYY as character), use INREC to convert it to a comparable format first.
123OPTION EQUALS SORT FIELDS=(1,8,CH,A,9,8,CH,D) SUM FIELDS=NONE
If bytes 9–16 contain a date in YYYYMMDD character form, sorting that field descending puts the latest date first per customer. SUM FIELDS=NONE keeps that record. If the date is in another format (e.g. packed), use the appropriate format and length in SORT FIELDS= (e.g. 9,4,PD,D for a 4-byte packed date).
| Goal | Sort order | Effect |
|---|---|---|
| Maximum value per group | Key, then value descending (D) | Largest value is first; SUM FIELDS=NONE keeps it |
| Minimum value per group | Key, then value ascending (A) | Smallest value is first; SUM FIELDS=NONE keeps it |
| Earliest date per group | Key, then date ascending (A) | Earliest date first; SUM keeps that record |
| Latest date per group | Key, then date descending (D) | Latest date first; SUM keeps that record |
When two or more records have the same key (and same value when you sort by key and value), EQUALS tells DFSORT to preserve their relative order. So the “first” in the group is the one that appeared first in the sort output—i.e. your intended min or max. NOEQUALS allows the sort to reorder records with equal keys for performance; then which record SUM keeps is not guaranteed. For MIN/MAX you want a predictable first record, so use OPTION EQUALS.
If two records have the same key and the same value (e.g. same customer and same amount), you can add more sort fields to break the tie. For example sort by (customer, amount D, date D) so that when amount is equal, the latest date is first. Then SUM FIELDS=NONE keeps that record. The order of tie-breaker fields determines which “first” you get when values match.
With a single SORT/SUM step you get either the min record or the max record per key, not both. To get both, you need two separate outputs: one sort with value ascending and SUM FIELDS=NONE (for min), and one with value descending and SUM FIELDS=NONE (for max). That typically means two SORT steps or a more complex flow (e.g. ICETOOL with multiple operators or a custom program). Some shops use a single pass that writes the sorted data and then run two SUM steps with different logic, but the usual approach is two sort/SUM runs.
Imagine a row of boxes, one per kid. Each box has many cards with numbers. You want one card per box: the card with the biggest number. First you sort the cards in each box so the biggest number is on top. Then you take just the top card from each box. That’s “max per box.” For “smallest number,” you sort so the smallest is on top and take that card. The computer does the same: sort so the record you want (max or min) is first in the group, then SUM FIELDS=NONE means “keep only the first one in each group.”
1. How do you get the record with the maximum value in a field for each key group in DFSORT?
2. How do you get the minimum value per group?
3. Why is OPTION EQUALS important when doing MIN/MAX with SUM FIELDS=NONE?
4. You want the record with the earliest date per customer. What sort order do you use?
5. Can you get both MIN and MAX for the same key in one DFSORT step?