QUERY › QUERY52 / 67

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeQUERY

QUERY: order by and limit

A motorbike dealer in Palembang rewards the 3 salespeople with the most units sold this month. The manager wants a leaderboard that updates itself whenever the sales data changes.

Task

In E1, write one QUERY formula over A1:C9 that shows the salesperson (A) and units sold (C), sorted from the most units, and only the top 3 rows (plus headers).

Cells to fillE1:F4

Required functionsQUERY

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
order by column desc sorts from large to small; asc (the default) sorts from small to large.
Hint 2
limit n caps the number of result rows and comes after order by.
Hint 3
The clause order is select …, then order by … desc, then limit ….

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

E1

=QUERY(A1:C9,"select A, C order by C desc limit 3",1)

Explanation

QUERY sorts every row by column C in descending order, then limit 3 keeps only the first three rows: Candra, Fikri and Desi. QUERY clauses have a fixed order: select, where, group by, pivot, order by, limit, offset, label. Swapping them (for example limit before order by) makes the formula fail. Use offset to skip rows, for example limit 3 offset 3 for ranks 4 to 6.

Starting worksheet: Sheet1
Row	A	B	C
1	Sales	Area	Units sold
2	Anton	Ilir Barat	14
3	Bunga	Plaju	9
4	Candra	Ilir Timur	21
5	Desi	Kertapati	17
6	Evan	Sukarami	11
7	Fikri	Plaju	19
8	Gina	Ilir Barat	8
9	Hadi	Sukarami	16
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result