How does the SQL version of RANK work?

ORDER BY amount DESC ranks the largest amount first, like order 0 (descending) in RANK. Tied values share a rank and the next rank is skipped, which is how Excel's RANK also behaves. PARTITION BY region restarts the ranking per region, something that needs extra formulas in Excel.

Why can SQL give a different result from RANK?

DENSE_RANK does not skip numbers after ties, matching RANK.EQ only after you adjust it; check which behaviour you want.

RANK in SQL

RANK gives each value its position in a list; SQL does it with the RANK window function, which can also restart for each group.

The Excel version

=RANK(C2, C$2:C$100, 0)

The SQL equivalent

This example assumes: sales(id bigint, region text, amount numeric, sale_date date). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       region,
       amount,
       RANK() OVER (ORDER BY amount DESC) AS overall_rank,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales
ORDER BY overall_rank;

How it works

ORDER BY amount DESC ranks the largest amount first, like order 0 (descending) in RANK.
Tied values share a rank and the next rank is skipped, which is how Excel's RANK also behaves.
PARTITION BY region restarts the ranking per region, something that needs extra formulas in Excel.

Where the results differ from Excel

DENSE_RANK does not skip numbers after ties, matching RANK.EQ only after you adjust it; check which behaviour you want.
Excel ranks within a fixed range; SQL ranks over whatever rows the query returns, so a WHERE clause changes the ranks.
Nulls are ranked first or last depending on the database; add NULLS LAST for sales with no amount.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Rank each sale by amount, overall and within its region.
Who are the top three reps in each region?