How do I find the second-highest distinct value with a subquery?

Select distinct scores in a subquery, sort them from highest to lowest, then use LIMIT 1 OFFSET 1 to return the second value:

SELECT score
FROM (
  SELECT DISTINCT score
  FROM results
  WHERE score IS NOT NULL
  ORDER BY score DESC
  LIMIT 1 OFFSET 1
);

The inner query removes duplicate scores before sorting and skipping the highest one. The outer query returns the remaining score as a result row. SQLite supports ordering results and limiting how many rows are returned; a SELECT enclosed in parentheses is a subquery. [S1] [S2] The WHERE condition explicitly excludes missing scores.

The query returns 92. DISTINCT reduces the values to 100, 92, and 85 before OFFSET 1 skips the highest value. Without DISTINCT, sorting the original rows and skipping one row would still leave a 100 at the top: the second row would also have the top score. That is why DISTINCT belongs in the inner query, before its ordering and limit are applied. [S1]

The distinct values, in order, are:

1. 100
2. 92
3. 85

The query selects item 2.

The filter states that missing scores should not be included among the values being ranked. If score can be NULL, decide how you want those values handled; SQLite’s guidance notes that NULL handling should be considered. [S1]

With the filter shown, if there are fewer than two distinct non-NULL scores, the query returns no rows. [S1] If you instead use the subquery as a scalar expression, SQLite returns NULL when the inner SELECT has no row. [S2] Those are different result shapes: no result row versus one row whose value is NULL.

With the four example scores in the table, run the query above and expect one row containing 92. Replace results and score with your actual table and column names. The query selects values and does not change table contents.

Before relying on the result, check that the query matches your intended meaning:

1. Confirm the column contains the values you want to rank.
2. Keep DISTINCT if ties should count as one value.
3. Use ORDER BY score DESC to rank highest first.
4. Keep LIMIT 1 OFFSET 1 to skip one distinct value and take the next.
5. Decide whether NULL scores should be excluded.
6. Check whether fewer than two eligible distinct values is possible.

This query finds one overall second-highest distinct value; it does not calculate a separate result for each group.