Sort a Data Table

[#1]

Problem

You need to sort a table using one or more columns as the sort key.

Solution

Use the table's sort method, which supports ascending and descending order and in-place or new table sorting.

// run these lines, one at a time, to see the effects
// (place the cursor on the line and use the Enter key
// on the numeric keypad to easily submit the line.)	

dt = Open( "$sample_data/big class.jmp" );

// example 1: makes a new table
new table = dt << sort( by( height, weight ) );
Wait( 2 );
Close( new table, nosave );

// example 2: updates table in place
dt << sort( by( height, weight ), order( descending ), replace table );
Wait( 2 );
Close( dt, nosave );

// example 3: keep a column of original row order
dt = Open( "$sample_data/big class.jmp" );
orig = dt << New Column( "original row", formula( Row() ) );
dt << runFormulas; // make sure the background formula evaluation is done, then
orig << deleteFormula; // delete the formula so it won't run after the sort (!)
dt << sort( by( height, weight ), order( descending ), replace table );
Wait( 2 );
dt << sort( by( orig ), order( ascending ), replace table );
Wait( 2 );
Close( dt, nosave );

Discussion

The final example shows adding a new column to capture the original sort order and how to use that new column to resore the original order. The documentation shows another useful option to name the table.

new table = dt << sort( by( height, weight ), Output Table Name("Fred") );

See Also

Documentation

[MathStatChem]

i think the statement

orig = dt << New Column( "original row", formula( Row() ) );
dt << runFormulas; // make sure the background formula evaluation is done, then
orig << deleteFormula; // delete the formula so it won't run after the sort (!)

be more efficient as

 dt << New Column( "original row", set values( 1::(N Rows(dt)) );

.

Thanks for the example!

[Craige_Hales]

Good point. Rather than evaluate a formula for each row, then remove the formula, just build the sequence as a matrix (the :: operator) and initialize the column directly. Thanks!