topic Select rows with minimum value that meets some criteria in Discussions

Select rows with minimum value that meets some criteria

BHarris — Sat, 10 Jun 2023 23:57:10 GMT

Starting with:

Open( "$SAMPLE_DATA/Big Class.jmp" );

... I'd like to select the records of the shortest male student at each age who is at least 60" tall, i.e., Tim (age 12, 60" tall), Joe (age 13, 63" tall), Frederick (age 14, 63" tall), Mark (age 15, 62" tall), Phillip (age 16, 68" tall), Kirk (age 17, 68" tall).

I've tried a half-dozen different ways to do this with either formula columns or "select where..." and haven't been able to come up with an approach to do this.

Any ideas?

Re: Select rows with minimum value that meets some criteria

jthi — Thu, 17 Nov 2022 07:45:51 GMT

One option which should work

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt << Select Where(
	:sex == "M" & :height >= 60 & :height == Col Min(:height, :sex, :height >= 60, :age)
);

Re: Select rows with minimum value that meets some criteria

BHarris — Mon, 21 Nov 2022 17:38:18 GMT

Wow, impressive!

I would love to understand this a little better -- specifically I don't understand how the Col Min is treating argument #3. I get what it's doing with args #2 and #4, but I'm really surprised that you can put an expression in there instead of just a column label. Any comments on how you think about those arguments?

Thank you!

Re: Select rows with minimum value that meets some criteria

jthi — Mon, 21 Nov 2022 17:43:00 GMT

It is just like the other byVars, it will get value 1 if :height is over or equal to 60 and 0 if not.

Split it into parts and you can test it (create new column with the :height >= 60 and then use that instead in the Col Min()).