Smallest

Find the Nth smallest number in a column. Write it into a new column on every row.

What it does

Smallest scans one number column. It sorts the values from low to high. It takes the value at position N. That one value goes on every row.

Before. A short price list:

After. Column price, N = 2, new column second_cheapest:

The sorted prices are 15, 20, 45 and 90. Position 2 is 20.

Why put one value on every row

A constant column is easy to compare against. Add a Smallest column, then compute the gap to the minimum in an Add Column step.

Add the Nth smallest value

1. Type smallest in the Search transforms box, in the Pipeline panel.
2. Select Smallest in the results. The panel opens below the grid.
3. Open Search and select a column… under Select column. The list holds only number columns.
4. Type a number in N (rank). Type 1 for the minimum.
5. Keep New Column (Number) selected under Apply results into.
6. Type a name in New Column Name.
7. Click Apply.

The grid updates at once. The step appears in your pipeline, where you can edit or delete it later.

To write over a column that exists, click Existing Column at step 5. Then pick a column in Select existing column….

Tips

• Empty cells are ignored. They do not take a position in the sort.
• Repeated values keep their positions. Two rows at 15 make 15 the answer for N = 1 and N = 2.
• Leave N (rank) empty for the minimum. The step then uses 1.
• An N above the value count gives an empty column.
• The panel warns you when the table has no number columns. Run Convert Type on the column first.
• For a minimum for each group, use Group & Aggregate with MIN.

For SQL users

The step adds one scalar subquery to every row:

SELECT *, (SELECT "price" FROM data WHERE "price" IS NOT NULL ORDER BY "price" ASC LIMIT 1 OFFSET 1) AS "second_cheapest" FROM data

Related Operations

• Pivot - Pivot table (rows to columns)
• Window Function - Rank, row number, lead, lag
• Largest - Get nth largest value as a column