Select rows with maximal values in one column

[#1]

I can use summary to find the maximums (multiple due to groups) in one column. However, I also want to look up in which rows in the original table the maximums occur.

Is there an easy solution? Thanks for your help!

[#2]

You can match the original table with the summary table using Update (Tables menu) with group column(s) = group column(s) and data = max(data) as matching columns. The maximum rows will be denoted by non-missing cells in the added NRows column.

My first answer was based on sloppy thinking...


Message was edited by: MS

[#3]

MS,

Thanks. I gave it a try and I am still selecting all rows in the original table when selecting the 2nd summary table. Whenever I select one row (Max for the group) in the summary table, every row in the first summary table in the group also gets selected.

I hope I am following your instruction correctly. Is the 2nd summary table named as "DataTable By (group, data) By (group)" ?

FYI, I am using JMP7.

Dong

[#4]

Sorry, you´re right. I was too quick to answer. I will edit my erroneous post above

What I wanted to suggest is that you can try to combine Summary with Update (or Join, I am not sure Update is found in the JMP7 Tables Menu).

Here's a script example that should work but may be ineffective for very large data tables.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << clear select;
Summarize( g = by( :sex ), m = Max( :age ) );
For( j = 1, j <= N Items( g ), j++,
	For Each Row( If( :sex == g[j] & :age == m[j], Row State() = Selected State( 1 ) ) )
);

[#5]

The script looks good. I will give it a try and report back.

What surprised me is that JMP itself does not keep the index (row number) of the data maximum somewhere.

Dong

[#6]

For Each Row() can be inefficient for large tables.

Select Where() will probably be faster:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << clear select;
Summarize( g = by( :age ), m = Max( :height ) );

dt<<Select Where(:height==m[loc(g,char(:age) )]);

[#7]

Hello, sorry to reply to an old thread, but I cannot find relevant examples elsewhere.

I would like to summarize by group, then select those rows that have max weight by group.

I was able to Summarize, but I don't know how to select those rows.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << clear select;
Summarize( g = by( :age, :sex ), m = Max( :weight ) );

/* How to select those rows summarized by group?

Thank you

[#8]

Fantastic solution. This is exactly what I was looking for!

[#9]

I got JMP10 and revisited this problem.  "Join" using column matching  works well.  I guess table "update" seems would be even more straightforward.

[#10]

Rows->Row Selection->Select Dominant will select the rows with the maximum (or minimum) values in a column or group of columns.

I believe that'll get you what you're looking for.

Jeff