Staff (Retired)

Joined:

Mar 21, 2013

Choose Language Hide Translation Bar

## 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") );``

Documentation

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!

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!

Article Labels
Article Tags
Contributors