I am doing the following Table File Request and need to limit the number of records to the first 5 records each time the clno changes (each unique clno). Thanks.

TABLE FILE HOLDFILE
SUM PAYMENTS
BY CLNO PAGE-BREAK REPAGE NOPRINT
BY DESCRIPTION
END

Windows Operating System
WF Developer Studio 8009

This message has been edited. Last edited by: Michele Brooks,

If DESCRIPTION is the "differentiator" field, then BY LOWEST 5 DESCRIPTION might work...

If any of the other BY HIGHEST/LOWEST don't work, then COMPUTE a counter and use WHERE TOTAL counter LE 5.

Lowest 5 will only give me the lowest 5 for the first clno only. Thank you for your suggestion. I figured out the following solution. This solution will start the list count with 1 every time the clno changes. This allows me to capture the first 5 records for each unique clno.

 
TABLE FILE HOLD1
LIST *
BY CLNO PAGE-BREAK REPAGE
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
DEFINE FILE HOLD2
RECORD_COUNT/I5 = LIST;
END
TABLE FILE HOLD2
SUM
BY CLNO PAGE-BREAK REPAGE NOPRINT
WHERE RECORD_COUNT LE 5
END

Michelle, because there is a BY statement before the BY LOWEST you should get 5 rows for every CLNO.

This seems to work:

SET BYDISPLAY=ON
TABLE FILE GGPRODS
SUM
SIZE
BY PACKAGE_TYPE
BY LOWEST 3 PRODUCT_DESCRIPTION
END

Package Product        Size 
------- -------------- ----
Case    Biscotti       24 
Case    Coffee Grinder 12 
Case    Coffee Pot      8 
Pounds  French Roast   12 
Pounds  Hazelnut       16 
Pounds  Kona           12

That's nice Michelle. I didn't know that you could use LIST as a variable name afte using it as a command. I wonder if RANKED BY works the same ...

Yes you can, but as it is a reserved word, you can't use it in the TABLE request.

quote:

TABLE FILE HOLD1
LIST *
BY CLNO PAGE-BREAK REPAGE
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
DEFINE FILE HOLD2
RECORD_COUNT/I5 = LIST;
END

Michelle uses LIST (a reserved word) in her define. You think that if I put something like

RECORD_NUMBER/I5=RANK;

in a define it won't work? Maybe I'll give it a try. There's one routine I run every 6 months where it might be useful.