Largest

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

What it does

Largest reads one number column. It sorts the values from high to low. It picks the value at position N. Every row gets that same value.

Before. Peak temperatures in four cities:

After. Column temp_c, N = 3, new column third_hottest:

What N does

Add the Nth largest value

1. Type largest in the Search transforms box, in the Pipeline panel.
2. Select Largest in the results. The panel opens below the grid.
3. Open Search and select a column… under Select column. Only number columns appear.
4. Type a number in N (rank).
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.

Write into a column that exists

Click Existing Column under Apply results into. Then open Select existing column… and pick a column. The step replaces every value in that column.

Tips

• Empty cells drop out of the sort. They never win a position.
• Equal values each take a position. Two rows at 41 give 41 for N = 1 and N = 2.
• An N above the value count gives an empty column.
• Use Smallest for the other end of the range.
• To see the top rows and not the top value, use Top / Bottom Rows.
• To get a maximum for each group, use Group & Aggregate with MAX.

For SQL users

The step adds one scalar subquery to every row:

SELECT *, (SELECT "temp_c" FROM data WHERE "temp_c" IS NOT NULL ORDER BY "temp_c" DESC LIMIT 1 OFFSET 2) AS "third_hottest" FROM data

Related Operations

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