The row behind the maximum in SQL

MAX gives you the value. Getting the row it came from is a different question.

SELECT MAX(score) AS best
FROM players;

SELECT *
FROM players
ORDER BY score DESC
LIMIT 1;

SELECT *
FROM players
WHERE score = (
        SELECT MAX(score)
        FROM players
    );

How it works

1. An aggregate returns one value, not the row it was found on.
2. ORDER BY ... LIMIT 1 returns the whole winning row.
3. The subquery form finds every row tied for the maximum.

Keywords and builtins used here

• AS
• BY
• DESC
• FROM
• LIMIT
• MAX
• ORDER
• SELECT
• WHERE

The run, in numbers

Lines

14

Characters to type

164

Tokens

38

Three-star pace

80 tpm

At the three-star pace of 80 tokens a minute, this run takes about 28 seconds.

Type this snippet

Step 4 of 4 in Aggregation, step 4 of 22 in Joins & aggregation.

← Previous Next →