Skip to content
Common mistakes / SQL: Why Sorting by a Rounded Value Changes the Top N
Common mistake

SQL: Why Sorting by a Rounded Value Changes the Top N

When a rounded column keeps its original name, ORDER BY sorts the rounded value. Values that were different now tie, and the tie-breaker decides who makes the top N.

The symptom

You want Movie Rentals' three best-rated films, shown with whole-number ratings, so you write round(rating) as rating and sort by rating desc, then title. Paper Lanterns (8.7) is first, as it should be. Second and third are Echoes (8.0) and Midnight Diner (7.9), while The Long Night (8.4) and Neon Harbor (8.1) are left out.

Why it happens

ORDER BY looks up names in the SELECT list first. The alias rating hides the table's rating, so the query sorts the rounded values. Six films round to 8, which makes them a tie, and the tie-breaker you added, title, picks Echoes and Midnight Diner because they come first alphabetically.

The fix

Give the rounded column a name of its own. Then rating in ORDER BY means the table's column, with its full precision:

Wrong: ORDER BY reads the rounded alias
select title, round(rating) as rating
from movies
order by rating desc, title
limit 3
Right: a new alias, so ORDER BY reads the raw rating
select title, round(rating) as rounded_rating
from movies
order by rating desc, title
limit 3

Round for display, and sort by the value you rounded from. If the column has to be called rating in the output, qualify the sort instead: order by movies.rating desc reads the table's column whatever the alias is.

Rounding first can be right

Sometimes the rounded value is the ranking you mean: "every film rated about 8 ties". Then say so, and break the tie on purpose:

Ties on purpose, broken by the raw rating 
select title,
       round(rating) as rating_band
from movies
order by rating_band desc, rating desc, title
limit 3
Dialect note

PostgreSQL, MySQL, SQL Server, and SQLite resolve the name the same way: an output column named in ORDER BY wins over a table column with the same name. WHERE and GROUP BY work differently in some of them, so reusing a column's name as an alias is risky across the board.

Learn more

Related

Mistake
Why rank() = 1 Returns More Than One Row

Tied rows share rank 1; break the tie on purpose.

Mistake
LIMIT Without ORDER BY Gives Unpredictable Rows

Sort before you limit, or top-N is arbitrary.

Next · Why dates stored as text filter wrong →