Sorting and Limiting Result Sets

Why Sorting and Limiting Matter

When you run a query, the database returns a set of rows. If you do not explicitly request an order, you should assume the rows can come back in any order. Sometimes it looks consistent during testing, but it can change after an index is added, statistics change, data grows, or the optimizer chooses a different plan. Sorting and limiting are the tools you use to make result sets predictable and to focus on the most relevant rows, such as “the newest 10 orders” or “the top 5 products by revenue.”

This chapter focuses on two related goals: (1) arranging rows in a meaningful order, and (2) returning only a subset of rows (for example, the first page of results). You will learn how to use ORDER BY, how to control ascending vs. descending order, how to handle ties and NULL values, and how to limit results using syntax that varies slightly across database systems.

Sorting Rows with ORDER BY

ORDER BY is the clause that defines the order of rows in the final output. Conceptually, it happens after the database has determined which rows and columns to return. Practically, the optimizer may use indexes to avoid sorting, but you should think of ORDER BY as the final step that makes the output ordered.

Basic ORDER BY

To sort by one column, list that column in ORDER BY. By default, sorting is ascending (smallest to largest, A to Z, oldest to newest depending on the data type).

SELECT product_id, product_name, price
FROM products
ORDER BY price;

This returns products from the lowest price to the highest price.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Ascending vs. Descending

Use ASC for ascending and DESC for descending. If you omit them, most databases assume ASC.

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC;

This returns the most expensive products first.

Sorting by Multiple Columns

You can sort by more than one column. The database sorts by the first column; if there are ties (equal values), it sorts those tied rows by the next column, and so on.

SELECT customer_id, last_name, first_name
FROM customers
ORDER BY last_name ASC, first_name ASC;

This is a common pattern for alphabetical lists: last name first, then first name.

Sorting by Expressions

You are not limited to raw columns. You can sort by an expression, such as a computed value. For example, suppose you want to sort line items by their extended amount (quantity times unit price):

SELECT order_id, product_id, quantity, unit_price,
       quantity * unit_price AS line_total
FROM order_items
ORDER BY quantity * unit_price DESC;

Many databases also allow you to sort by the alias (line_total) instead of repeating the expression, but support varies. If you want maximum portability, repeat the expression in ORDER BY.

Sorting by Column Position (Use Carefully)

Some databases allow ORDER BY 1 to mean “order by the first selected column,” ORDER BY 2 for the second, and so on:

SELECT last_name, first_name
FROM customers
ORDER BY 1, 2;

This can be convenient, but it is fragile: if you change the SELECT list, the meaning of the numbers changes. For beginner-friendly, maintainable SQL, prefer explicit column names.

Sorting Text, Numbers, and Dates: Practical Notes

Text Sorting and Case Sensitivity

Text sorting depends on collation rules (language and case rules). In some systems, Apple and apple may sort together; in others, uppercase may come before lowercase. If you need a case-insensitive sort in a case-sensitive collation, you can sort by a normalized expression (for example, LOWER(name)), but be aware that this can reduce the ability to use indexes for ordering.

SELECT username
FROM users
ORDER BY LOWER(username);

Numeric Sorting vs. “Numbers Stored as Text”

If a numeric value is stored in a text column, ordering may be surprising: 100 can come before 20 because text sorts character by character. The fix is to store numbers in numeric columns. If you cannot change the schema, you may need to cast to a numeric type in ORDER BY (syntax varies by database).

SELECT invoice_no
FROM invoices
ORDER BY CAST(invoice_no AS INTEGER);

Date/Time Sorting

Proper date/time types sort chronologically. If you store dates as text, ordering depends on the format. ISO-like formats (YYYY-MM-DD) sort correctly as text; other formats often do not. Prefer real date/time types whenever possible.

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC;

Handling NULLs in Sorting

NULL represents “unknown” or “missing.” When sorting, databases differ in where they place NULL values by default. Some treat NULL as lowest, others as highest, and behavior can differ between ascending and descending sorts.

Many databases support explicit control using NULLS FIRST or NULLS LAST:

SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST;

If your database does not support NULLS FIRST/LAST, you can often simulate it with an expression that sorts NULL values separately. One common technique is to sort by a “is null” expression first, then by the real value.

SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY CASE WHEN commission_pct IS NULL THEN 1 ELSE 0 END,
         commission_pct DESC;

This pushes NULL commissions to the bottom (because they get 1) and sorts non-NULL commissions descending.

Limiting Result Sets

Limiting means returning only a certain number of rows, such as the first 10 rows after sorting. This is essential for dashboards, previews, and pagination. The important idea is: limiting without sorting is usually meaningless. If you ask for “10 rows” without an ORDER BY, you are effectively asking for “any 10 rows.”

Common LIMIT Syntax Variations

Different databases use different keywords. Here are the most common patterns:

• PostgreSQL, MySQL, MariaDB, SQLite: LIMIT n and optionally OFFSET m
• SQL Server: TOP (n) in the SELECT list, and modern versions also support OFFSET ... FETCH with ORDER BY
• Oracle: modern versions support FETCH FIRST n ROWS ONLY (often with OFFSET), older versions use ROWNUM patterns

Because this course is “any database,” focus on the concept and recognize the syntax you need for your system.

Top N: Return the First N Rows After Sorting

Example goal: “Show the 5 most recent orders.” The key is to sort by a date/time column descending, then limit to 5.

SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 5;

Equivalent ideas in other systems:

-- SQL Server style
SELECT TOP (5) order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC;

-- Oracle / ANSI style
SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC
FETCH FIRST 5 ROWS ONLY;

Notice what stays the same: the ORDER BY defines what “top” means. Without it, “top 5” is not well-defined.

Offset: Skipping Rows (Pagination)

Pagination means returning results in pages: page 1 shows rows 1–20, page 2 shows rows 21–40, etc. The typical approach is LIMIT plus OFFSET (or OFFSET ... FETCH).

Example: “Show page 3 of customers, 10 per page, ordered by last name then first name.” Page 3 means skip the first 20 rows and return the next 10.

SELECT customer_id, last_name, first_name
FROM customers
ORDER BY last_name, first_name
LIMIT 10 OFFSET 20;

SQL Server / ANSI-style pagination:

SELECT customer_id, last_name, first_name
FROM customers
ORDER BY last_name, first_name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Two critical rules for pagination:

• Always use a deterministic order. If multiple rows can tie on your sort columns, the “boundary” between pages can shift between executions.
• Use a stable tie-breaker. Add a unique column (often the primary key) as the last sort key to make ordering fully deterministic.

SELECT customer_id, last_name, first_name
FROM customers
ORDER BY last_name, first_name, customer_id
LIMIT 10 OFFSET 20;

Now, even if two customers share the same name, the order is still consistent because customer_id breaks ties.

Step-by-Step: Building a “Top N” Query Correctly

In practice, beginners often write a limit first and then wonder why the results look random. Use this step-by-step approach to avoid that.

Step 1: Decide what “top” means

“Top” must be defined by a measurable column or expression: newest by date, highest by amount, longest by duration, etc.

Example: “Top 10 products by price.” The measurable value is price.

Step 2: Write the query returning the needed columns

SELECT product_id, product_name, price
FROM products;

Step 3: Add ORDER BY to define the ranking

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC;

Step 4: Add a tie-breaker if needed

If multiple products can have the same price, add a stable secondary sort. Often this is the primary key.

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC, product_id ASC;

Step 5: Add the limit syntax for your database

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC, product_id ASC
LIMIT 10;

This produces a predictable “top 10” list.

Step-by-Step: Pagination with Stable Ordering

Let’s build a paginated list of orders for an admin screen. Assume you want 25 orders per page, ordered by newest first.

Step 1: Choose the ordering columns

Newness is usually created_at. But many orders can share the same timestamp (especially if timestamps are stored with low precision). Add order_id as a tie-breaker.

Step 2: Write page 1

SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 25 OFFSET 0;

Step 3: Write page 2

Page 2 skips 25 rows.

SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 25 OFFSET 25;

Step 4: Calculate offset for any page

If page_number starts at 1, then:

offset = (page_number - 1) * page_size

So for page 5 with page size 25, offset is 100.

Limiting with “Top Percent” and “With Ties” (Database-Specific)

Some systems offer extra options that can be useful but are not universal.

TOP ... WITH TIES (SQL Server)

Sometimes you want “top 10, but include any additional rows tied with the 10th.” For example, if the 10th and 11th products have the same price, you might want both. SQL Server supports:

SELECT TOP (10) WITH TIES product_id, product_name, price
FROM products
ORDER BY price DESC;

Other databases can emulate this with window functions, but that is typically covered in more advanced chapters.

FETCH FIRST ... WITH TIES (Some Systems)

Some databases support FETCH FIRST n ROWS WITH TIES. If your database supports it, it behaves similarly: it may return more than n rows if there are ties at the boundary.

Common Mistakes and How to Avoid Them

Mistake: Limiting without ordering

If you run:

SELECT order_id, created_at
FROM orders
LIMIT 10;

You are not guaranteed the “first 10” in any meaningful sense. Fix it by deciding the order and adding ORDER BY:

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Mistake: Non-deterministic ordering in pagination

If you paginate using only a non-unique column, rows can appear on multiple pages or disappear between page loads when new rows are inserted or when ties exist.

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 25 OFFSET 25;

Fix it by adding a unique tie-breaker:

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 25 OFFSET 25;

Mistake: Sorting by formatted values

If you format numbers or dates into strings and then sort by those strings, you can get incorrect ordering. Sort by the raw numeric/date column, and format only for display in the application layer or in a separate expression that is not used for ordering.

Mistake: Assuming NULL placement

If your report expects missing values at the bottom, but your database sorts NULL first, the output will look wrong. Use NULLS LAST if available, or a CASE expression to control placement.

Performance Notes (Practical, Not Theory-Heavy)

Sorting can be expensive on large tables because the database may need to compare many rows and potentially write temporary data. A few practical guidelines help keep queries responsive:

• Sort only when you need to. If the consumer of the query does not require ordering, omit ORDER BY.
• Return fewer columns when possible. Sorting wide rows (many large columns) can require more memory and I/O.
• Use indexes that match your ordering. If an index exists on the same columns and direction as your ORDER BY, the database may be able to read rows already in order, avoiding a separate sort step.
• Be careful with expressions in ORDER BY. Sorting by LOWER(name) or quantity * unit_price can prevent the database from using a simple index order unless you have an appropriate computed/indexed expression feature.
• OFFSET can get slower for deep pages. Skipping 100,000 rows means the database still has to walk past them. For large datasets, many applications use “keyset pagination” (also called seek pagination) based on the last seen sort key, but that is a more advanced pattern.

Practice Tasks

Task 1: Create a price list

Write a query that returns product name and price, sorted by price ascending, and then by product name ascending for products with the same price.

SELECT product_name, price
FROM products
ORDER BY price ASC, product_name ASC;

Task 2: Show the newest records

Write a query that returns the 15 most recent orders (order id and created date/time). Ensure ties are stable by adding the order id as a secondary sort key.

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 15;

Task 3: Paginate a directory

Return page 4 of a customer directory with 20 rows per page, ordered by last name, first name, and customer id.

SELECT customer_id, last_name, first_name
FROM customers
ORDER BY last_name, first_name, customer_id
LIMIT 20 OFFSET 60;

Task 4: Control NULL placement

List employees sorted by commission percentage descending, but put employees with no commission at the bottom.

SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST;

If NULLS LAST is not supported, use:

SELECT employee_id, last_name, commission_pct
FROM employees
ORDER BY CASE WHEN commission_pct IS NULL THEN 1 ELSE 0 END,
         commission_pct DESC;