LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: chrisg1978

Does anyone know how to use Lavastorm nodes to duplicate the Max or Min function in SQL? I have a need to group data based on a set of fields and only grab the record with the lowest value in a certain field for each group.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rabbott

Please review the following post and see if it provides you with any clues. Note that you'll need first download the first .zip attachment, then replace the .brg graph file that was in the .zip archive with the .brg file from the subsequent post:

http://community.mda-data.com/showthread.php?t=86

You may need to substitue "firstInGroup" for "lastInGroup" in the Agg node to obtain the lowest value. There are also the groupMax and groupMin macros you may wish to try. You can find more information in the BRAINScript Language Help file.

Thanks,

- Bob

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Chris,

Good Day.
A good way of finding the min/max of a particular field is to use the Aggregate node. You would set it up to aggregate on every record, and use the "max" or "min" BRAINscript functions as you went to determine the value you want.

For example, in an Agg you could have something similar to the following:
(I want the maximum value for the ID field)

if firstInGroup then best = 0
else
best = max(best,'ID')

emit best as BEST
where lastInGroup

(my groupby statement is 1, to use every record)

Or flip it with the MIN function.

Does this help?
Regards
Timon Koufopoulos
MDA Support.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: roeperg

Instead of 0 it might be better to assign the current value of 'ID' when firstInGroup, otherwise MIN will possibly return erroneous data (or MAX if the maximum value happens to be negative).

if firstInGroup then best = 'ID'
else
best = min(best,'ID')

emit best as BEST
where lastInGroup

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Patman

First, sort the Records by the field you want to get the Min and Max of.

Then connect to an aggregate node with the following code:

if firstInGroup then Min = 'FIELD_ID'

emit Min, 'FIELD_ID' as Max where lastInGroup