1Memory vs Disk
When you run an ORDER BY, the database tries to sort the data in RAM (memory). If the dataset is too large for RAM, it writes temporary 'Spill Files' to the hard drive, sorts them there, and reads them back. This destroys query performance. Proper indexing prevents this by storing the data pre-sorted on disk.
2Step-by-Step Breakdown
The Default Order. In SQL, tables have NO inherent order. If you run 'SELECT * FROM users', the database might return them in the order they were inserted, or it might return them completely randomly based on how the hard drive is spinning.
ORDER BY. To guarantee a specific order, you MUST use the 'ORDER BY' clause at the very end of your query. 'SELECT * FROM users ORDER BY age;'.
ASC vs DESC. By default, ORDER BY sorts in Ascending order (A-Z, 0-9). To reverse this, add the 'DESC' keyword. 'ORDER BY age DESC;' returns the oldest users first.
Multiple Columns. You can sort by multiple columns. 'ORDER BY last_name ASC, first_name ASC;'. It sorts everyone by last name first. If two people have the same last name, it then sorts them by first name.
Knowledge Check. If you execute a SELECT query without an ORDER BY clause, in what guaranteed order will the database return the rows?
- →In the exact order they were inserted
- →There is no guaranteed order
Sorting by Alias. Remember the execution order? ORDER BY runs AFTER the SELECT clause. This means you CAN use aliases in the ORDER BY clause! 'SELECT name AS n FROM users ORDER BY n;'.
Sorting by Position. A lazy developer trick: You can sort by the column's index number in the SELECT statement. 'SELECT first, last, age FROM users ORDER BY 3 DESC;'. (Sorts by age). Don't use this in production.
NULL Values in Sorting. When you sort a column that contains NULLs, Postgres puts NULLs at the very end by default. You can force them to the top using 'NULLS FIRST'.
Performance Impact. Sorting 10 million rows in memory is incredibly expensive and slow. If you frequently sort by 'created_at', you must add an Index to that column.
Summary. If the order matters to your frontend, you must explicitly declare it.
Level Up 🚀
Advanced cheat sheets, SEO tricks, and interview prep for this topic.
Browser Support
Fully supported.
Fully supported.
Fully supported.
Fully supported.
Accessibility (A11y)
1Announce Current Sort Order in Sortable Data Tables
A UI table with clickable column headers for sorting (built on ORDER BY) should expose the current sort column and direction via aria-sort="ascending"/"descending" on the <th> element, so screen reader users know how the table is currently ordered without relying on a visual arrow icon alone.
SEO Implications
- 1
Sort Order Choices Can Create Duplicate Content URLs If Not Canonicalized
Pages offering multiple sort options (e.g. ?sort=price_asc vs ?sort=price_desc) serve the same underlying items in different order. Without a canonical tag pointing to a single default-sorted version, search engines may index near-duplicate sorted variants of the same page separately.
Best Practices
Index Columns You Frequently Sort By, Especially created_at or Similar
Sorting large tables in memory is expensive; if the sort column has a B-Tree index, the database can read rows already in order directly from the index instead of sorting on the fly, which is dramatically faster at scale.
Explicitly Handle NULLS FIRST or NULLS LAST When the Sort Column Can Be NULL
Default NULL placement in ORDER BY varies by database engine. Being explicit with NULLS FIRST/NULLS LAST avoids surprising, engine-dependent behavior when the sort column can be empty.
Frequent Bugs
A frontend list appears to render rows in a random or inconsistent order, especially after data changes.
Tables have no inherent row order in SQL — without an explicit ORDER BY, the database can return rows in whatever order is internally convenient, and that order isn't guaranteed to stay stable between queries. Always add an explicit ORDER BY on a unique or stable column.
Sorting by ordinal position (e.g. ORDER BY 3) breaks silently after a teammate reorders the SELECT column list.
ORDER BY 3 refers to whichever column is currently third in the SELECT list, not a fixed column name — if someone adds or reorders a column, the sort silently targets the wrong data. Always sort by the explicit column name or alias instead of a positional number.
Real-World Examples
Sorting a Leaderboard by Score With NULLs Handled Explicitly
A leaderboard needed to rank players by score, but some players hadn't completed a game yet and had a NULL score that needed to appear at the bottom, not mixed in with real scores.
SELECT username, score
FROM players
ORDER BY score DESC NULLS LAST;