HexLibraryHexLibrary
</> HexCodeSign inSign up
PythonJavaScriptTypeScriptCC++JavaPHPGoRustRubyBashSQLLuaPerlCOBOLPascalDelphiFortranDartSwiftC#JuliaGroovyRElixirHaskellKotlinScalaClassic ASPClipperMS-DOS
LESSONS
Introduction to SQL (SELECT)Tables (CREATE & INSERT)Filtering (WHERE)Sorting & LimitingAggregate FunctionsUpdate & DeleteJoins (JOIN)
REFERENCE
📖 Overview
🔎 Querying (SELECT)
🗃️ Creating a Table
✏️ Inserting & Modifying Data
🧲 Filtering (WHERE)
🔢 Sorting & Limiting
📊 Aggregate Functions
🔗 Joins (JOIN)
SQL›Sorting & Limiting

Sorting & Limiting

Sorting with ORDER BY (ASC/DESC) and limiting the number of results with LIMIT.

Simple enough for a 5-year-old to understand

ORDER BY

Example code
1
CREATE TABLE k(name TEXT, score INTEGER);
2
INSERT INTO k VALUES ('Alex', 95), ('Sam', 88), ('Zoe', 99);
3
SELECT name FROM k ORDER BY score DESC;
Highest to lowest: Zoe / Alex / Sam
Output / InputHighest to lowest: Zoe / Alex / Sam
Output / Input
💡 Hint: Default is ascending (ASC); add DESC for descending.

About this topic

ORDER BY determines the order in which the returned rows will appear. If you don't specify this, the database gives NO guarantee of order — the same query can return results in a different order at different times. This is the source of many surprising bugs.

Sorting is usually used together with LIMIT: "top ten best-selling products", "last five orders". Without this duo, it is impossible to extract a meaningful list from a large table. Pagination is built with the same logic, by adding OFFSET.

What you will learn

  • Sorting in ascending and descending order with ORDER BY
  • Sorting by multiple columns
  • Truncating the result with LIMIT
  • Setting up pagination with OFFSET
  • Where NULL values fall in the sort order

When to use?

Sorting is required on every screen that shows a list to the user: newest content, highest rated, alphabetical user list. Putting results in a meaningful order when generating reports is also the foundation of readability.

When paginating, it is essential that the sorting includes a UNIQUE column; otherwise, the order of rows with equal values may change between pages, and some records might never appear or appear twice.

Common mistakes

Wrong

SELECT * FROM posts LIMIT 10;

Using LIMIT without ORDER BY leaves it unclear which ten rows will come. If you want "ten newest posts", you must explicitly specify the sorting; otherwise, the result can be considered random.

Correct

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;

Wrong

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

If there are rows with equal created_at values, their order can change on every run; some records will be lost or duplicated between page 2 and page 3. Adding a unique column to the sorting solves this.

Correct

SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 20;

Wrong

SELECT name, total FROM orders
ORDER BY 2 DESC;

Sorting by column number works but is fragile: the day you add a new column to the SELECT list, the query will silently sort by another column. Typing the column name is always safe.

Correct

SELECT name, total FROM orders
ORDER BY total DESC;

Frequently asked questions

What happens if I don't write ORDER BY?

The database returns rows in whatever order it finds most convenient, and this order can change — when the table is updated, the index changes, or a different query plan is chosen. If order matters to you, it must be explicitly specified.

Where do NULL values sort?

This varies depending on the database: some put NULLs at the beginning, others at the end. To finalize the behavior, it is necessary to explicitly specify it in systems that support the NULLS FIRST or NULLS LAST clause, and to handle the NULL case separately in the sort expression in systems that do not support it.

Why is OFFSET slowing down on very large tables?

Because the database has to read the rows to be skipped as well; to go to the hundred-thousandth page, all preceding rows are scanned. In large datasets, cursor-based pagination in the form of "fetch the ones greater than the last seen ID" is used instead of OFFSET.

Summary

Without ORDER BY, row order is not guaranteed; using it together with LIMIT is almost mandatory. Adding a unique column to the sort in pagination prevents records from being lost or duplicated.

HexLibraryHexLibrary

Learn programming more clearly. Every command is explained line by line with colors, reinforced with runnable exercises, test your code instantly — all in one place, free.

Learn

  • Home
  • Languages & Lessons
  • HexCode

Company

  • About
  • Contact

Legal

  • Privacy Policy
  • Terms of Use

Other Projects

  • HexToolz
  • Free Service Manuals
© 2026 HexLibrary · The clearest way to learn code