Tabellab

ORDER BY & LIMIT: Rank, Top 3 with LARGE and INDEX/MATCH

Lesson 2 of 813 minLab

All lessons

SELECT & WHERE: Filter Rows with Flags and COUNTIFSLab13 minFree
ORDER BY & LIMIT: Rank, Top 3 with LARGE and INDEX/MATCHLab13 minFree
GROUP BY with COUNT, SUM & AVG: COUNTIFS, SUMIFS, AVERAGEIFS TablesLab14 min Pro
HAVING: Filter Groups on Their AggregatesLab13 min Pro
INNER JOIN: XLOOKUP and the #N/A Rows a Join DropsLab15 min Pro
LEFT JOIN & NULLs: IFNA, 'Unknown' and Unmatched RowsLab14 min Pro
DISTINCT & Duplicates: First-Occurrence COUNTIFLab13 min Pro
CASE WHEN & Date Functions: IFS Buckets and YEAR/MONTH GroupingLab15 min Pro

You are not signed in — the lab works, but your progress is not saved. Start free

Read first

The SQL

SELECT product, revenue
FROM products
ORDER BY revenue DESC
LIMIT 3;

-- rank of every row (a window function)
SELECT product, RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM products;

SELECT product FROM products
ORDER BY units ASC
LIMIT 1;

The spreadsheet equivalent

RANK() OVER (ORDER BY revenue DESC) → =RANK.EQ(E2,$E$2:$E$13,0)
ORDER BY revenue DESC LIMIT 3 → LARGE(E2:E13, 1 … 3) + INDEX/MATCH for the name
ORDER BY units ASC LIMIT 1 → MIN(D2:D13) + INDEX/MATCH
SQL Server writes SELECT TOP 3 … instead of LIMIT 3; the idea is the same

Loading the spreadsheet…