6  Sorting and taking

Which three orders were the largest of the year? Two verbs answer it together, and they nearly always travel as a pair: sort puts the rows in an order, and take keeps the first few of them.

run('
  sales
    then sort [revenue] descending
    then take 3
')
date region product quantity revenue cost
2026-01-26 West Gadget 5 300 200
2026-05-06 North Gadget 4 240 160
2026-03-03 East Doohickey 5 200 125 
sales |> sort(descending(revenue)) |> take(3)
date region product quantity revenue cost
2026-01-26 West Gadget 5 300 200
2026-05-06 North Gadget 4 240 160
2026-03-03 East Doohickey 5 200 125 
sales >> sort(descending(col.revenue)) >> take(3)
date region product quantity revenue cost
2026-01-26 West Gadget 5 300 200
2026-05-06 North Gadget 4 240 160
2026-03-03 East Doohickey 5 200 125

sales then sort [revenue] descending then take 3

take on its own is honest but rarely what you meant. It keeps the first rows in whatever order the table happens to be in, which is fine for peeking at a table and wrong for answering a question. The sort in front is what turns “some three rows” into “the three largest”, and that distinction returns with force at the end of this chapter.

6.1 Why there is no ascending

descending reverses one key, and there is no matching word ascending, because ascending is what happens when you do not ask for anything. A word that means “do the default” is a second way to say the same thing, and every second way costs every reader a small doubt. If both spellings exist, do they differ? In this grammar, if two spellings existed they would differ, so the redundant one is absent. This is the smallest example of the one-way rule you will meet, and the cheapest place to get used to it.

6.2 Several keys

Sorting by several keys reads left to right: the first key decides, the second breaks its ties, and so on down.

run('
  sales
    then sort [product], [revenue] descending
')
date region product quantity revenue cost
2026-03-03 East Doohickey 5 200 125
2025-12-05 West Doohickey 3 120 75
2026-02-14 North Doohickey 2 80 50
2026-01-26 West Gadget 5 300 200
2026-05-06 North Gadget 4 240 160
2025-11-17 East Gadget 2 120 80
2026-09-15 East Gadget 1 60 40
2026-06-19 East Sprocket 10 150 120
2026-07-08 West Sprocket 6 90 72
2026-01-09 East Widget 8 200 80
2025-12-22 North Widget 6 150 60
2026-10-02 West Widget 5 125 50
2025-11-03 West Widget 4 100 40
2026-08-24 North Widget 3 75 30
2026-04-11 West Widget 2 50 20 
sales |> sort(product, descending(revenue))
date region product quantity revenue cost
2026-03-03 East Doohickey 5 200 125
2025-12-05 West Doohickey 3 120 75
2026-02-14 North Doohickey 2 80 50
2026-01-26 West Gadget 5 300 200
2026-05-06 North Gadget 4 240 160
2025-11-17 East Gadget 2 120 80
2026-09-15 East Gadget 1 60 40
2026-06-19 East Sprocket 10 150 120
2026-07-08 West Sprocket 6 90 72
2026-01-09 East Widget 8 200 80
2025-12-22 North Widget 6 150 60
2026-10-02 West Widget 5 125 50
2025-11-03 West Widget 4 100 40
2026-08-24 North Widget 3 75 30
2026-04-11 West Widget 2 50 20 
sales >> sort(col.product, descending(col.revenue))
date region product quantity revenue cost
2026-03-03 East Doohickey 5 200 125
2025-12-05 West Doohickey 3 120 75
2026-02-14 North Doohickey 2 80 50
2026-01-26 West Gadget 5 300 200
2026-05-06 North Gadget 4 240 160
2025-11-17 East Gadget 2 120 80
2026-09-15 East Gadget 1 60 40
2026-06-19 East Sprocket 10 150 120
2026-07-08 West Sprocket 6 90 72
2026-01-09 East Widget 8 200 80
2025-12-22 North Widget 6 150 60
2026-10-02 West Widget 5 125 50
2025-11-03 West Widget 4 100 40
2026-08-24 North Widget 3 75 30
2026-04-11 West Widget 2 50 20

descending wraps the key it reverses, not the whole sort, so mixed directions are ordinary: products forward, revenue backward, exactly as written.

6.3 The latest one of each

Here is the pair doing its best work. Which was the most recent order in each region? In most tools this question has a name you have to learn: slice_max, nlargest, a window function with a filter. Here it is the two verbs you already have, plus by:

run('
  sales
    then sort [date] descending
    then take 1 by [region]
    then pick [region, date, product]
')
region date product
West 2026-10-02 Widget
East 2026-09-15 Gadget
North 2026-08-24 Widget 
sales |>
  sort(descending(date)) |>
  take(1, by = region) |>
  pick(region, date, product)
region date product
West 2026-10-02 Widget
East 2026-09-15 Gadget
North 2026-08-24 Widget 
(sales
  >> sort(descending(col.date))
  >> take(1, by = col.region)
  >> pick(col.region, col.date, col.product))
region date product
West 2026-10-02 Widget
East 2026-09-15 Gadget
North 2026-08-24 Widget

sales then sort [date] descending then take 1 by [region] then pick [region, date, product]

Sort newest first, take the first row of each region. The dates in this book are written 2026-10-02 style, and text in that shape sorts correctly as text, which is why no date machinery was needed. The chapter on dates introduces the real date words, for when text alone is not enough.

6.4 The other end

take gives the rows a pipeline reaches first. take_last gives the ones at the far end, and the pair reads as what they do.

run('
  sales
    then sort [revenue]
    then take_last 3
    then pick [product, revenue]
')
product revenue
Doohickey 200
Gadget 240
Gadget 300 
sales |> sort(revenue) |> take_last(3) |> pick(product, revenue)
product revenue
Doohickey 200
Gadget 240
Gadget 300 
sales >> sort(col.revenue) >> take_last(3) >> pick(col.product, col.revenue)
product revenue
Doohickey 200
Gadget 240
Gadget 300

sales then sort [revenue] then take_last 3 then pick [product, revenue]

The answer comes back in the order the sort asked for, smallest first, and the three rows are the three largest. Sorting the other way and taking the first three gets the same rows in the opposite order. That is a different table, and usually not the one that was wanted.

by works here as it does on take:

run('
  sales
    then sort [revenue]
    then take_last 1 by [region]
    then pick [region, product, revenue]
')
region product revenue
East Widget 200
North Gadget 240
West Gadget 300 
sales |> sort(revenue) |> take_last(1, by = region) |> pick(region, product, revenue)
region product revenue
East Widget 200
North Gadget 240
West Gadget 300 
sales >> sort(col.revenue) >> take_last(1, by = col.region) >> pick(col.region, col.product, col.revenue)
region product revenue
East Widget 200
North Gadget 240
West Gadget 300

sales then sort [revenue] then take_last 1 by [region] then pick [region, product, revenue]

6.5 When the cut falls inside a tie

take 3 gives three rows. If the third and fourth are level on the sort key, one of them is in and one is out, and nothing in the table says which. The engine picked. That is fine when you wanted three rows and wrong when you wanted the top three scores.

with ties says the second thing: keep every row level with the last one taken.

scores <- data.frame(
  who    = c("ana", "ben", "cal", "dee"),
  points = c(10, 9, 9, 8)
)

run('
  scores
    then sort [points] descending
    then take 2 with ties
', scores = scores)
who points
ana 10
ben 9
cal 9 
scores |> sort(descending(points)) |> take(2, ties = TRUE)
who points
ana 10
ben 9
cal 9 
scores = pd.DataFrame({
    "who":    ["ana", "ben", "cal", "dee"],
    "points": [10, 9, 9, 8],
})

scores >> sort(descending(col.points)) >> take(2, ties=True)
who points
ana 10
ben 9
cal 9

scores then sort [points] descending then take 2 with ties

Three rows came back from take 2, and that is the point. Once you ask for ties you have stopped asking for a number of rows and started asking for a cut. The count is no longer something you can read off the sentence. That is why it has to be asked for and is never the default.

It needs a sort in front of it, and more sharply than the others do: level with the last row on what? With nothing sorted there is no answer, so the sentence is refused rather than guessed.

collect(scores |> take(2, ties = TRUE))
Error:
! 
illegal: `with ties` keeps the rows level with the last one taken, and nothing has said what they would be level *on*. Sort before it: `then sort [points] descending then take 3 with ties`
  |
2 |   then take 2 with ties
  |        ^^^^^^^^^^^^^^^^
try:
    collect(scores >> take(2, ties=True))
except GodError as refusal:
    print(refusal)

illegal: `with ties` keeps the rows level with the last one taken, and nothing has said what they would be level *on*. Sort before it: `then sort [points] descending then take 3 with ties`
  |
2 |   then take 2 with ties
  |        ^^^^^^^^^^^^^^^^

take_last takes it too, and means the same thing at the other end.

If you are arriving from dplyr, this is the one place its default and this one differ. slice_max(points, n = 2) keeps ties unless you tell it not to, and take 2 drops them unless you tell it to keep them. Neither is wrong. Writing it down is what stops the two from quietly disagreeing.

6.6 What travels with it

  • descending belongs to the key it wraps, and only sort accepts it.
  • by after take means what by means everywhere: do this per group. The chapter on the small words returns to it.
  • take composes with everything that settles an order. Of the windowed words in giving each row a place, row_number leans on sort the way take ... by does below, and rank says what it goes by itself, needing no sort at all.

6.7 What it refuses

The first row of each group means nothing until something has said first by what. take ... by without a sort in front is refused, not guessed at:

sales |> take(1, by = region) |> collect()
Error:
! 
illegal: `take ... by` gives the first rows of each group, and nothing has said what order the rows are in, so there is no first. Sort before it: `then sort [when] descending then take 1 by [id]`
  |
2 |   then take 1 by [region]
  |        ^^^^^^^^^^^^^^^^^
try:
    collect(sales >> take(1, by = col.region))
except GodError as refusal:
    print(refusal)

illegal: `take ... by` gives the first rows of each group, and nothing has said what order the rows are in, so there is no first. Sort before it: `then sort [when] descending then take 1 by [id]`
  |
2 |   then take 1 by [region]
  |        ^^^^^^^^^^^^^^^^^

A bare take is allowed and a grouped one is not, and the asymmetry is the honest one. Peeking at some rows of a table is a real thing to want. But “the first of each group, by no particular order” is not a question: it is a coin toss dressed as one. A tool that answered it would be choosing your answer for you.

take_last asks for a sort even ungrouped, and the reason is the same one read from the other side. The rows a pipeline reaches first are at least the rows it reached first. The rows at the far end are a claim about an end, and a table does not have one until something says which way it runs.

sales |> take_last(3) |> collect()
Error:
! 
illegal: `take_last` gives the rows at the far end, and nothing has said which end that is. Sort before it: `then sort [when] then take_last 3`. For the rows a pipeline reaches first, `take` needs no sort
  |
2 |   then take_last 3
  |        ^^^^^^^^^^^
try:
    collect(sales >> take_last(3))
except GodError as refusal:
    print(refusal)

illegal: `take_last` gives the rows at the far end, and nothing has said which end that is. Sort before it: `then sort [when] then take_last 3`. For the rows a pipeline reaches first, `take` needs no sort
  |
2 |   then take_last 3
  |        ^^^^^^^^^^^

The message names take as the word that needs no sort. Wanting a few rows to look at is the common reason to arrive here, and that is the verb for it.