Finding the Second Highest Value in Excel and in SQL

"Who is the runner-up?" sounds like a one-line question and turns out to hide a decision. If two salespeople tied with 500, is the second highest 500 or the next number down? A spreadsheet and a database each answer that question in a default way, and the defaults differ. This article gives the formulas and queries for both readings, in Excel and in SQL, with the NULL and empty cases that catch people. It is one of the worked pairs in moving between Excel and SQL.

Sample data

Sales per person, in B2:B7 in the sheet and in a table sales(person, amount) in the database:

The highest value is 500, held by two people. The question is what comes second.

Two readings of "second highest"

Counting duplicates (position). Sort descending: 500, 500, 420, 380, 380. The second value in that list is 500. Under this reading, the second highest is the value in position two.

Distinct values. Remove duplicates first: 500, 420, 380. The second is 420. Under this reading, the second highest is the second different value.

Neither is wrong. A leaderboard of ranks wants the distinct reading ("who is in second place"). A statistic about the order wants the positional one. Say which you mean.

Excel: counting duplicates

=LARGE(B2:B7, 2)

LARGE(array, k) returns the kth largest value, and it counts duplicates, so this returns 500. Blank cells are ignored, and text is ignored. If k is larger than the number of numeric values, the result is #NUM!.

Excel: distinct second highest

Several formulas work, depending on the Excel version.

=LARGE(UNIQUE(B2:B7), 2)

In Microsoft 365, UNIQUE removes duplicates first (a blank counts as a value, but LARGE ignores it), so the result is 420. For versions without UNIQUE, use MAXIFS to find the largest value that is below the maximum:

=MAXIFS(B2:B7, B2:B7, "<"&MAX(B2:B7))

This returns 420: the maximum of the values less than 500. MAXIFS needs Excel 2019 or later. For older versions, an array-style formula does the same:

=MAX(IF(B2:B7<MAX(B2:B7), B2:B7))

entered with Ctrl+Shift+Enter in versions before dynamic arrays. A helper column with a rank (=RANK.EQ(B2, $B$2:$B$7)) and a lookup for rank 2 also works, but with a tie for first, the rank sequence is 1, 1, 3, 4, 4, so no row has rank 2. That gap is the same reason RANK is the wrong tool for the distinct reading (rank customers by spend in Excel and SQL with ties handled goes through it).

To return the person and not only the value, wrap in a lookup: =XLOOKUP(420, B2:B7, A2:A7), or compute the value in one cell and look up from it. If several people share the second value, a lookup returns the first; use FILTER to list them all.

SQL: counting duplicates

SELECT amount
FROM sales
WHERE amount IS NOT NULL
ORDER BY amount DESC
LIMIT 1 OFFSET 1;

Sorted descending, skipping the first row and taking the next gives 500. The WHERE amount IS NOT NULL is important: PostgreSQL sorts NULL as larger than any value, so in a descending sort a NULL would come first, push everything down one place and make the "second" result the actual top. Alternatively state ORDER BY amount DESC NULLS LAST. The default differs between databases: PostgreSQL and Oracle put NULLs first in descending order, MySQL and SQL Server put them last. State it explicitly and the query is portable in intent.

SQL: distinct second highest

SELECT DISTINCT amount
FROM sales
WHERE amount IS NOT NULL
ORDER BY amount DESC
LIMIT 1 OFFSET 1;

DISTINCT collapses the duplicates before the offset, so the result is 420. A version with a window function reads better when you may need other ranks:

SELECT amount
FROM (
  SELECT amount, dense_rank() OVER (ORDER BY amount DESC NULLS LAST) AS r
  FROM sales
) t
WHERE r = 2
GROUP BY amount;

dense_rank gives equal values the same rank and does not skip numbers (1, 1, 2, 3, 3), so rank 2 is the second distinct value. The GROUP BY amount collapses the several rows that share rank 2. A third form uses a subquery for the maximum:

SELECT max(amount)
FROM sales
WHERE amount < (SELECT max(amount) FROM sales);

It reads like the MAXIFS formula, and it returns NULL when there is no second distinct value, which is a graceful result for a one-value table. The OFFSET forms return no row in that case. Choose by what the caller expects: a NULL or an empty result.

Second highest per group

Often the question is per group: the runner-up in each region. In SQL, a window function partitions:

SELECT region, person, amount
FROM (
  SELECT region, person, amount,
         dense_rank() OVER (PARTITION BY region ORDER BY amount DESC NULLS LAST) AS r
  FROM sales
) t
WHERE r = 2;

For the positional reading, use row_number() with a tiebreaker (ORDER BY amount DESC, person) so the result is deterministic. In Excel, the per-group value is =LARGE(IF(A2:A100=G2, B2:B100), 2) as an array formula (it works as typed in Microsoft 365), or with FILTER: =LARGE(FILTER(B2:B100, A2:A100=G2), 2). For the distinct form, add UNIQUE inside: =LARGE(UNIQUE(FILTER(B2:B100, A2:A100=G2)), 2).

Edge cases to decide on

Fewer than two values. LARGE returns #NUM!. The SQL offset form returns no rows. Handle it: =IFERROR(LARGE(UNIQUE(B2:B7), 2), "n/a") for an expected case, with IFNA or a count test so that other errors stay visible (error handling with IFERROR and safer alternatives).

Blanks and NULLs. Excel ignores blank cells in LARGE. SQL ignores NULL in aggregates but not in ORDER BY. Zero is a value in both: a column where empty means zero should be filled in first, or the second highest could be 0.

Text numbers. A column of numbers stored as text is skipped by LARGE and, in SQL, either errors or sorts as text ("500" before "420" before "1000" is a different order). Convert types first (fixing numbers stored as text).

Floating point near-ties. Values like 0.1 + 0.2 and 0.3 are not equal in binary floating point. If amounts are computed, round before ranking: ROUND(x, 2) in Excel and round(x, 2) on numeric in SQL.

Checking the answer

Work the sample by hand, as above, and compare. Then test the awkward inputs: all values equal (distinct second does not exist), a single value, a blank row in the middle, a tie for second. A short list of test inputs with expected results for both readings catches the divergences. For the sample data, the positional answer is 500 and the distinct answer is 420, and any translation that returns something else has a type or NULL problem.

When the second highest is the wrong question

If you want the top few, ask for the top N: LIMIT n in SQL, TAKE(SORT(...), n) or LARGE with SEQUENCE in Excel, and let the reader see the ties. If you want a position in a leaderboard, a rank column with a stated tie rule says more than a single value. If you want to know how far ahead the leader is, subtract the second distinct value from the first. And if the data keeps changing, a query or view that produces the ranking is better than a formula someone has to remember. The PostgreSQL manual covers window function syntax and ORDER BY with NULLS LAST, and Microsoft's page on LARGE lists its behavior.