Sort
Put rows in order by one or more columns, each ascending or descending. Rank keeps every row, sorts by the same keys and adds a position column, optionally restarting within groups so you can take the top rows per group.
Sort a score table
Manual Input has five people and their scores. Sort puts the highest scores first, then orders equal scores by name.
Sort interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| name | score |
|---|---|
| Ada | 12 |
| Sam | 12 |
| Lin | 8 |
| Bea | 3 |
| Noor |
- Change Direction for
scoretoAscending. Bea's score of 3 comes first, followed by Lin's 8 and the two scores of 12. Noor's missing score stays last. - Set
scoreback toDescending, then changenametoDescending. Sam now appears before Ada among the people with a score of 12. - Use Move up beside the
namekey to put it abovescore. Names now decide the order first; scores only break ties between equal names.
When to use
Use Sort to order rows, or to add a rank column for top N lists within groups. To search by similar text, use Fuzzy Search.
Read the output
With score Descending followed by name Ascending, the output is Ada (12), Sam (12), Lin (8), Bea (3), then Noor (NULL).
Ada and Sam share the highest score. The second key puts Ada first because her name comes before Sam's. Noor's blank score is NULL and stays last in either score direction.
Sort keeps all five rows and both columns. It changes row order without changing any values or types.
Sort values using the right type
Manual Input reads score as BIGINT, so scores are sorted numerically.
For other datasets, check that the column's type matches the values you want to order. VARCHAR uses text order, which puts 12 before 3 when ascending. DATE and TIMESTAMP sort chronologically. Use Select if you need to change a column's type first.
To take the first few rows after sorting, use Sample.
To number rows in the sorted order, add Record ID.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
Sort Keys is an ordered list. The first key has the highest priority; each later key breaks ties left by the earlier keys.
- Mode
Sort reorders rows. Rank also sorts by the keys, keeps every row and adds a position column. Default: Sort.
- Add
Add a sort key, then choose its Column and Direction. Additional keys break ties in the keys above them.
- Column
Choose the input column to sort by. Sorting uses its current data type.
- Direction
Ascending orders numbers from smallest to largest, text from A to Z and dates from earliest to latest. Descending reverses that order. Missing values (NULL) come last in either direction. Default: Ascending.
- Move up / Move down
Move a key earlier or later in the list to change its priority. This changes how rows are sorted, not the order of columns in the output.
- Remove row (×)
Remove that sort key from the list. This doesn't delete data rows or columns. At least one sort key is required.
- Method
How ties are handled. Rank shares a value and skips (1, 1, 3); Dense shares without skipping (1, 1, 2); Row # breaks ties by input order (1, 2, 3); Percent scales the rank to 0..1. Every input row stays and one column is added; the other columns are untouched. Nulls sort last. Combine with Filter (e.g. rank <= 3) for top-N per group.
- Group By
Optional columns that restart the ranking within each group, e.g. rank sales within each region. Leave empty to rank over all rows together.
- Output Column
Name of the new column holding the rank.
Sort FAQs
- Does Sort change values or remove rows?
- No. In Sort mode, only row order changes. All input rows, column names, values and data types are kept. Rank mode also adds one column.
- Why does 12 appear before 3 when I sort ascending?
- The column may contain text. Text is sorted character by character, so 12 comes before 3. Convert the column to a numeric type with Auto Field or Select before sorting.
- Where do missing values appear?
- NULL values come after non-NULL values in both Ascending and Descending order. Later sort keys can order rows whose earlier key values are both NULL.
- What if rows tie on every sort key?
- They keep their input order.
- Can I leave Sort Keys empty?
- No. Sort requires at least one key with a selected column. An empty list produces an error.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.