Break By & By

[rash]

Can anyone give me the scenario to differentiate the break by & by?

 

[RealHeavyDude]

BY is the sort order in which the data is fetched. Adding the BREAK keyword enables the aggregate functions like FIRST-OF, LAST-OF, AVERAGE and so on.

Heavy Regards, RealHeavyDude.

 

[Stefan]

And remember, a dynamic indexed BREAK BY will triple your record reads.

An unindexed BY will double your record reads (dynamic and static).
An unindexed BREAK BY will double static record reads and quadruple dynamic reads.

The following table sums up the results on the sports database with 83 customer records:

query	static factor	dynamic factor
for each customer	1	1
for each customer by cust-num	1	1
for each customer by city	2	2
for each customer break by cust-num	1	3
for each customer break by city	2	4

Note that there is one record more or less read by the dynamic queries when hitting the end of the result set.

 

[veenit]

Thanks Safen, for such useful information.

A very simple scenerio -

Break-by will be used when we have to do some processing on either first or last record of group like-

The program demand a display like -

Sales Rep name Sale Date Price
ABC 10/12/07 7.2
10/13/07 8.2
10/14/07 9.2
XYZ 10/12/07 6.2

In this the sales rep name should appears only with first record. So a break by wil be good option here.

If sales rep name has to be display with every record, then instead of break by, only by should be used.