Chapter 10 of 35

ORDER BY & LIMIT

We can now choose columns (SELECT), choose rows (WHERE) and handle missing values (NULL). But we have not controlled one basic thing: the order of the rows. We have seen MySQL return rows in table order, then suddenly in a different order for no visible reason.

In this chapter we will learn ORDER BY, which sorts the result, and LIMIT, which returns only part of it. Together they answer some of the most common questions in real applications and interviews: "top 3 highest-paid employees", "latest 10 orders", "page 2 of the search results", and "the second-highest salary".

All examples use the shared company_db sample database, with real MySQL 8.4 output.


Why Do We Need ORDER BY?

A table in a relational database is, in theory, a set of rows, and sets have no order. In practice MySQL returns rows in whatever order is cheapest for it to read them. Here is a striking example from the SELECT Statement chapter. Selecting all columns from orders:

SELECT * FROM orders;
+----------+-------------+------------+---------+-----------+
| order_id | customer_id | order_date | amount  | status    |
+----------+-------------+------------+---------+-----------+
|     1001 |         101 | 2024-01-15 | 2500.00 | Delivered |
|     1002 |         102 | 2024-01-20 | 4800.00 | Delivered |
|     1003 |         101 | 2024-02-02 | 1200.00 | Cancelled |
|     1004 |         103 | 2024-02-14 | 7300.00 | Delivered |
|     1005 |         104 | 2024-03-01 |  950.00 | Shipped   |
|     1006 |         102 | 2024-03-11 | 3100.00 | Delivered |
|     1007 |         101 | 2024-03-28 | 5600.00 | Pending   |
|     1008 |        NULL | 2024-04-02 |  800.00 | Pending   |
+----------+-------------+------------+---------+-----------+
8 rows in set (0.00 sec)

These rows come out in order_id order. But SELECT order_id, customer_id, 'Online' AS channel FROM orders returned order 1008 first, then 1001, 1003, 1007..., because MySQL chose to read the smaller index on customer_id instead. Same table, same data, different order.

Without ORDER BY, the order of rows is not guaranteed. It can change when data grows, when an index is added, or when MySQL is upgraded. If order matters to you, ask for it.


ORDER BY Syntax

SELECT columns
FROM table
WHERE condition
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
  • ASC = ascending (smallest first). This is the default.

  • DESC = descending (largest first).

  • ORDER BY comes after WHERE (and after GROUP BY/HAVING, which you will meet later).

Ascending Order

SELECT name, salary FROM employees ORDER BY salary;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Meera Nair   |  48000.00 |
| Ishita Gupta |  55000.00 |
| Ananya Das   |  58000.00 |
| Vikram Singh |  62000.00 |
| Karan Patel  |  70000.00 |
| Arjun Reddy  |  82000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
| Sneha Kapoor | 110000.00 |
| Aarav Sharma | 150000.00 |
+--------------+-----------+
10 rows in set (0.01 sec)

ORDER BY salary and ORDER BY salary ASC are the same.

Descending Order

SELECT name, salary FROM employees ORDER BY salary DESC;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Sneha Kapoor | 110000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
| Arjun Reddy  |  82000.00 |
| Karan Patel  |  70000.00 |
| Vikram Singh |  62000.00 |
| Ananya Das   |  58000.00 |
| Ishita Gupta |  55000.00 |
| Meera Nair   |  48000.00 |
+--------------+-----------+
10 rows in set (0.00 sec)

How Different Types Sort

Type

Ascending means

Numbers

Smallest to largest

Dates

Oldest to newest

Strings

Dictionary (A to Z) order, according to the collation; case-insensitive by default in MySQL

Employees from the longest-serving to the newest:

SELECT name, hire_date FROM employees ORDER BY hire_date;
+--------------+------------+
| name         | hire_date  |
+--------------+------------+
| Sneha Kapoor | 2017-11-20 |
| Aarav Sharma | 2018-04-12 |
| Priya Iyer   | 2019-07-01 |
| Karan Patel  | 2019-09-23 |
| Rohan Mehta  | 2020-01-15 |
| Arjun Reddy  | 2020-10-05 |
| Vikram Singh | 2021-03-08 |
| Ananya Das   | 2022-06-30 |
| Meera Nair   | 2023-02-14 |
| Ishita Gupta | 2024-01-10 |
+--------------+------------+
10 rows in set (0.00 sec)

Alphabetical list of names:

SELECT name FROM employees ORDER BY name;
+--------------+
| name         |
+--------------+
| Aarav Sharma |
| Ananya Das   |
| Arjun Reddy  |
| Ishita Gupta |
| Karan Patel  |
| Meera Nair   |
| Priya Iyer   |
| Rohan Mehta  |
| Sneha Kapoor |
| Vikram Singh |
+--------------+
10 rows in set (0.00 sec)

A warning about storing numbers as text: if a VARCHAR column holds values like '9', '10', '100', it sorts them as strings: '10', '100', '9' (because '1' < '9'). Store numbers in numeric columns, as discussed in the SQL Data Types chapter.


Sorting by Multiple Columns

List employees by department, and within each department, highest salary first:

SELECT name, dept_id, salary
FROM employees
ORDER BY dept_id, salary DESC;
+--------------+---------+-----------+
| name         | dept_id | salary    |
+--------------+---------+-----------+
| Ishita Gupta |    NULL |  55000.00 |
| Aarav Sharma |      10 | 150000.00 |
| Priya Iyer   |      10 |  95000.00 |
| Rohan Mehta  |      10 |  95000.00 |
| Sneha Kapoor |      20 | 110000.00 |
| Vikram Singh |      20 |  62000.00 |
| Ananya Das   |      20 |  58000.00 |
| Karan Patel  |      30 |  70000.00 |
| Meera Nair   |      30 |  48000.00 |
| Arjun Reddy  |      40 |  82000.00 |
+--------------+---------+-----------+
10 rows in set (0.00 sec)

How it works:

  1. Rows are sorted by the first column, dept_id, ascending.

  2. Only when two rows have the same dept_id is the second column, salary DESC, used to break the tie.

Think of a phone contact list sorted by surname, then first name. The second key only matters inside a group of equal first keys.

Each column has its own direction. ASC/DESC applies only to the column right before it. A common mistake is writing ORDER BY dept_id, salary DESC and expecting both to be descending; here dept_id is still ascending. Flip it around:

SELECT name, dept_id, salary
FROM employees
ORDER BY dept_id DESC, salary;
+--------------+---------+-----------+
| name         | dept_id | salary    |
+--------------+---------+-----------+
| Arjun Reddy  |      40 |  82000.00 |
| Meera Nair   |      30 |  48000.00 |
| Karan Patel  |      30 |  70000.00 |
| Ananya Das   |      20 |  58000.00 |
| Vikram Singh |      20 |  62000.00 |
| Sneha Kapoor |      20 | 110000.00 |
| Priya Iyer   |      10 |  95000.00 |
| Rohan Mehta  |      10 |  95000.00 |
| Aarav Sharma |      10 | 150000.00 |
| Ishita Gupta |    NULL |  55000.00 |
+--------------+---------+-----------+
10 rows in set (0.00 sec)

Ties and Deterministic Order

Priya and Rohan both earn 95,000. When sorting only by salary, which one comes first? SQL does not say. MySQL happens to show Priya first here, but that is not guaranteed. If the order of tied rows matters (and with LIMIT it often does, as we will see), add a tie-breaker column, ideally a unique one like the primary key or name:

SELECT name, salary FROM employees ORDER BY salary DESC, name DESC LIMIT 4;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Sneha Kapoor | 110000.00 |
| Rohan Mehta  |  95000.00 |
| Priya Iyer   |  95000.00 |
+--------------+-----------+
4 rows in set (0.00 sec)

Now the order of Priya and Rohan is defined by the second key, every time.


What Can You ORDER BY?

A Column Alias

Remember the logical processing order: ORDER BY runs after the SELECT list, so unlike WHERE, it can use aliases:

SELECT name, salary * 12 AS annual FROM employees ORDER BY annual DESC;
+--------------+------------+
| name         | annual     |
+--------------+------------+
| Aarav Sharma | 1800000.00 |
| Sneha Kapoor | 1320000.00 |
| Priya Iyer   | 1140000.00 |
| Rohan Mehta  | 1140000.00 |
| Arjun Reddy  |  984000.00 |
| Karan Patel  |  840000.00 |
| Vikram Singh |  744000.00 |
| Ananya Das   |  696000.00 |
| Ishita Gupta |  660000.00 |
| Meera Nair   |  576000.00 |
+--------------+------------+
10 rows in set (0.00 sec)

A Column You Don't Display

You can sort by any column of the table, even if it is not in the SELECT list:

SELECT name FROM employees ORDER BY salary DESC LIMIT 3;
+--------------+
| name         |
+--------------+
| Aarav Sharma |
| Sneha Kapoor |
| Priya Iyer   |
+--------------+
3 rows in set (0.00 sec)

(One exception you will meet in the DISTINCT chapter: with SELECT DISTINCT, the sort columns must be in the select list.)

An Expression

Shortest names first, alphabetically among equal lengths:

SELECT name FROM employees ORDER BY LENGTH(name), name LIMIT 4;
+-------------+
| name        |
+-------------+
| Ananya Das  |
| Meera Nair  |
| Priya Iyer  |
| Arjun Reddy |
+-------------+
4 rows in set (0.00 sec)

Ananya Das, Meera Nair and Priya Iyer all have 10 characters, so the second key (name) puts them in alphabetical order. Arjun Reddy (11) comes next.

A practical expression sort is a custom order. Order statuses are not alphabetical in meaning; we want Pending, then Shipped, Delivered, Cancelled. MySQL's FIELD(value, list...) returns the position of the value in the list, which we can sort on:

SELECT order_id, status
FROM orders
ORDER BY FIELD(status, 'Pending', 'Shipped', 'Delivered', 'Cancelled'), order_id;
+----------+-----------+
| order_id | status    |
+----------+-----------+
|     1007 | Pending   |
|     1008 | Pending   |
|     1005 | Shipped   |
|     1001 | Delivered |
|     1002 | Delivered |
|     1004 | Delivered |
|     1006 | Delivered |
|     1003 | Cancelled |
+----------+-----------+
8 rows in set (0.00 sec)

FIELD is MySQL-specific. The portable way uses a CASE expression, covered in the CASE Expression chapter.

A Column Position

You can also write the position of a column in the SELECT list:

SELECT name, salary FROM employees ORDER BY 2 DESC, 1;

This sorts by the 2nd column (salary) descending, then the 1st (name). It gives the same 10 rows as ORDER BY salary DESC, name. It is handy when typing quick queries, but avoid it in saved code: if someone adds a column to the SELECT list, the meaning of 2 silently changes. A position larger than the number of columns is an error:

SELECT name, salary FROM employees ORDER BY 3;
ERROR 1054 (42S22): Unknown column '3' in 'order clause'

NULLs in Sorting

In MySQL, NULL is treated as smaller than any value when sorting. So NULLs appear first in ascending order and last in descending order:

SELECT name, city FROM employees ORDER BY city;
+--------------+-----------+
| name         | city      |
+--------------+-----------+
| Ishita Gupta | NULL      |
| Aarav Sharma | Bengaluru |
| Rohan Mehta  | Bengaluru |
| Priya Iyer   | Chennai   |
| Karan Patel  | Delhi     |
| Arjun Reddy  | Hyderabad |
| Meera Nair   | Kochi     |
| Ananya Das   | Kolkata   |
| Sneha Kapoor | Mumbai    |
| Vikram Singh | Mumbai    |
+--------------+-----------+
10 rows in set (0.00 sec)
SELECT name, city FROM employees ORDER BY city DESC;
+--------------+-----------+
| name         | city      |
+--------------+-----------+
| Sneha Kapoor | Mumbai    |
| Vikram Singh | Mumbai    |
| Ananya Das   | Kolkata   |
| Meera Nair   | Kochi     |
| Arjun Reddy  | Hyderabad |
| Karan Patel  | Delhi     |
| Priya Iyer   | Chennai   |
| Aarav Sharma | Bengaluru |
| Rohan Mehta  | Bengaluru |
| Ishita Gupta | NULL      |
+--------------+-----------+
10 rows in set (0.00 sec)

Other databases differ: PostgreSQL and Oracle treat NULL as larger than any value (NULLs last in ascending order) and support NULLS FIRST / NULLS LAST. MySQL does not support NULLS LAST, so we use a trick. The expression city IS NULL is 0 for real cities and 1 for NULL. Sort by it first:

SELECT name, city FROM employees ORDER BY city IS NULL, city;
+--------------+-----------+
| name         | city      |
+--------------+-----------+
| Aarav Sharma | Bengaluru |
| Rohan Mehta  | Bengaluru |
| Priya Iyer   | Chennai   |
| Karan Patel  | Delhi     |
| Arjun Reddy  | Hyderabad |
| Meera Nair   | Kochi     |
| Ananya Das   | Kolkata   |
| Sneha Kapoor | Mumbai    |
| Vikram Singh | Mumbai    |
| Ishita Gupta | NULL      |
+--------------+-----------+
10 rows in set (0.00 sec)

All the 0s (real cities) come before the 1 (NULL), and within the 0s, the second key sorts alphabetically. Similarly, ORDER BY city IS NULL DESC, city DESC would put NULLs first in a descending list.


LIMIT: Returning Only Some Rows

LIMIT n returns at most n rows. It is always the last step of the query, applied after sorting.

SELECT columns FROM table
WHERE ...
ORDER BY ...
LIMIT row_count;

Top-N Queries

The 3 highest-paid employees:

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Sneha Kapoor | 110000.00 |
| Priya Iyer   |  95000.00 |
+--------------+-----------+
3 rows in set (0.00 sec)

Step by step: MySQL sorts all 10 rows by salary descending, then keeps the first 3. The 2 lowest-paid:

SELECT name, salary FROM employees ORDER BY salary ASC LIMIT 2;
+--------------+----------+
| name         | salary   |
+--------------+----------+
| Meera Nair   | 48000.00 |
| Ishita Gupta | 55000.00 |
+--------------+----------+
2 rows in set (0.00 sec)

LIMIT combines naturally with WHERE. The two largest delivered orders:

SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'Delivered'
ORDER BY amount DESC
LIMIT 2;
+----------+-------------+---------+
| order_id | customer_id | amount  |
+----------+-------------+---------+
|     1004 |         103 | 7300.00 |
|     1002 |         102 | 4800.00 |
+----------+-------------+---------+
2 rows in set (0.00 sec)

Order 1007 (5,600) is bigger than 1002 but is Pending, so WHERE removed it before sorting.

The Tie Problem with LIMIT

Look again at the top-3 result. Priya earns 95,000, and so does Rohan, but only Priya made the "top 3". Which of the two appears is arbitrary unless you add a tie-breaker. And if the real question is "everyone with one of the top 3 salaries", LIMIT is the wrong tool: it counts rows, not distinct values. That question is answered properly with DENSE_RANK() in the Ranking Functions chapter.

LIMIT Without ORDER BY

SELECT name, salary FROM employees LIMIT 3;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
+--------------+-----------+
3 rows in set (0.00 sec)

This gives "some 3 rows". Which 3 is not defined. It is fine for peeking at a table, but never for answering a question like "first 3 employees hired".


OFFSET: Skipping Rows

OFFSET m skips the first m rows before LIMIT starts counting. MySQL accepts two spellings:

LIMIT row_count OFFSET skip     -- clear, also used by PostgreSQL and SQLite
LIMIT skip, row_count           -- MySQL shorthand: note the reversed order!

Both of these return rows 4, 5 and 6 (skip 3, take 3):

SELECT emp_id, name FROM employees ORDER BY emp_id LIMIT 3 OFFSET 3;
SELECT emp_id, name FROM employees ORDER BY emp_id LIMIT 3, 3;
+--------+--------------+
| emp_id | name         |
+--------+--------------+
|      4 | Sneha Kapoor |
|      5 | Vikram Singh |
|      6 | Ananya Das   |
+--------+--------------+
3 rows in set (0.00 sec)

The comma form is easy to misread, since the first number is the offset. LIMIT 3, 2 means "skip 3, take 2", not "take 3". Prefer LIMIT ... OFFSET ... for readability.

Other rules:

  • The offset is 0-based: OFFSET 0 starts at the first row.

  • If fewer rows remain than requested, you just get fewer: LIMIT 9, 3 on 10 employees returns only 1 row.

  • An offset past the end returns an empty set, not an error.

  • The values must be non-negative integer constants (or placeholders in prepared statements). Expressions such as LIMIT 3+1 or negative numbers are syntax errors.

  • LIMIT 0 returns an empty set. Tools use it to check a query is valid or to fetch column names without data.


Pagination

Websites show long lists one page at a time: search results, products, orders. With a fixed page size, page p (starting from 1) is:

OFFSET = (page_number - 1) × page_size
LIMIT  = page_size

Page size 3, 10 employees ordered by emp_id:
  Page 1: LIMIT 3 OFFSET 0  → emp 1, 2, 3
  Page 2: LIMIT 3 OFFSET 3  → emp 4, 5, 6
  Page 3: LIMIT 3 OFFSET 6  → emp 7, 8, 9
  Page 4: LIMIT 3 OFFSET 9  → emp 10        (last, partial page)
SELECT emp_id, name FROM employees ORDER BY emp_id LIMIT 3 OFFSET 9;
+--------+--------------+
| emp_id | name         |
+--------+--------------+
|     10 | Ishita Gupta |
+--------+--------------+
1 row in set (0.00 sec)

Two rules make pagination correct:

  1. Always ORDER BY, and by something unique (or with a unique tie-breaker). Otherwise rows with equal sort values can shift between pages, so a user may see the same row twice or miss one.

  2. Large offsets are slow. For OFFSET 100000, MySQL still has to produce and throw away 1,00,000 rows. Deep pages get slower and slower.

Keyset (Seek) Pagination

The fix for slow deep pages is to remember where the previous page ended and continue from there with WHERE, instead of counting rows to skip. If page 2 ended at emp_id 6:

SELECT emp_id, name, salary
FROM employees
WHERE emp_id > 6
ORDER BY emp_id
LIMIT 3;
+--------+-------------+----------+
| emp_id | name        | salary   |
+--------+-------------+----------+
|      7 | Karan Patel | 70000.00 |
|      8 | Meera Nair  | 48000.00 |
|      9 | Arjun Reddy | 82000.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)

With an index on emp_id (the primary key), MySQL jumps directly to 7 and reads 3 rows, whether it is page 3 or page 30,000. This is how "infinite scroll" feeds typically work. The trade-off: you can go to the "next" page easily but cannot jump straight to "page 57".


The Nth Highest Salary

"Find the second-highest salary" is one of the most asked SQL interview questions. With ORDER BY and LIMIT, the idea is: sort descending, skip N−1 rows, take 1.

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Sneha Kapoor | 110000.00 |
+--------------+-----------+
1 row in set (0.00 sec)

Correct. Now the 4th highest salary. The distinct salaries in descending order are 1,50,000, 1,10,000, 95,000, 82,000, so the answer should be 82,000:

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 3;
+------------+----------+
| name       | salary   |
+------------+----------+
| Priya Iyer | 95000.00 |
+------------+----------+
1 row in set (0.00 sec)

Wrong! Let's see why by numbering the sorted rows:

Row (offset)   name           salary
0              Aarav Sharma   150000
1              Sneha Kapoor   110000
2              Priya Iyer      95000  ┐ duplicate
3              Rohan Mehta     95000  ┘ salary     ← OFFSET 3 lands here
4              Arjun Reddy     82000               ← the real 4th highest

OFFSET counts rows, and duplicate salaries take up more than one row. (MySQL even printed "Priya Iyer" here, not Rohan, which shows again that the order of tied rows is arbitrary.) The fix is to remove duplicate salaries before skipping, using SELECT DISTINCT salary, which is exactly what the next chapter, DISTINCT, covers. Other approaches use subqueries or DENSE_RANK(), covered in later chapters.

Interview tip: when asked for the Nth highest salary, always ask "what about duplicate salaries?" Handling ties is what the interviewer is really testing.


Where ORDER BY and LIMIT Fit

Updating our clause order picture:

Written order:             Logical processing order:
SELECT ...                 1. FROM
FROM ...                   2. WHERE
WHERE ...                  3. GROUP BY
GROUP BY ...               4. HAVING
HAVING ...                 5. SELECT   (aliases created)
ORDER BY ...               6. DISTINCT
LIMIT ...                  7. ORDER BY (can use aliases)
                           8. LIMIT / OFFSET (last)

So WHERE filters first, ORDER BY sorts what is left, and LIMIT cuts the final sorted list. That is why "top 2 delivered orders" works: the filter runs before the cut.

Not Just MySQL: Other Syntaxes

LIMIT is not part of the SQL standard, so different databases spell "top N" differently:

Database

Top 3 rows

MySQL, PostgreSQL, SQLite

... ORDER BY salary DESC LIMIT 3

SQL Server

SELECT TOP 3 ... ORDER BY salary DESC

Standard SQL (Oracle 12c+, PostgreSQL, SQL Server 2012+ with OFFSET)

... ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY

MySQL 8.4 does not support FETCH FIRST; it gives a syntax error (1064). In MySQL, LIMIT can also be used with UPDATE and DELETE, for example to delete old rows in small batches (a MySQL extension; most databases do not allow it).


Performance Notes

  • Sorting costs work. When the sorted column has an index, MySQL can read rows already in order and stop after LIMIT rows; otherwise it must sort (a "filesort"). We will look at this in the Indexes chapter.

  • With LIMIT, MySQL does not have to fully sort everything: it can keep just the top N rows while scanning. Top-N queries are much cheaper than sorting the whole table.

  • ORDER BY RAND() LIMIT 1 picks a random row, but it gives every row a random number and sorts them all. Fine for small tables, very slow on big ones.


Common Mistakes

Mistake

What goes wrong

Fix

Relying on default row order

Order changes unpredictably

Always use ORDER BY when order matters

ORDER BY a, b DESC expecting both descending

a is still ascending

Write a DESC, b DESC

Reading LIMIT 3, 2 as "take 3"

Skips 3, takes 2

Use LIMIT 2 OFFSET 3

LIMIT without ORDER BY for "top N"

Random-ish rows

Add ORDER BY

Pagination ordered by a non-unique column

Rows repeat or vanish across pages

Add a unique tie-breaker

Nth highest with OFFSET on duplicate values

Wrong answer

Deduplicate first (DISTINCT) or use DENSE_RANK

Writing NULLS LAST in MySQL

Syntax error

ORDER BY col IS NULL, col

Putting ORDER BY before WHERE

Syntax error

Order: WHERE → ORDER BY → LIMIT


Interview Questions

What is the default sort order of ORDER BY?

Ascending (ASC).

Is the row order of a SELECT guaranteed without ORDER BY?

No. Relational tables are unordered; the database returns rows in whatever order its chosen plan produces, which can change.

How does ORDER BY with multiple columns work?

Rows are sorted by the first column; the second column is used only to order rows that tie on the first, and so on. Each column has its own ASC/DESC.

Can ORDER BY use a column alias? Can WHERE?

ORDER BY can, because it is evaluated after the SELECT list. WHERE cannot, because it is evaluated before.

Where do NULLs appear when sorting in MySQL?

First in ascending order, last in descending, because MySQL treats NULL as lower than any value. Use ORDER BY col IS NULL, col to put them last.

What does LIMIT 5, 10 mean in MySQL?

Skip 5 rows, then return the next 10 (rows 6 to 15). It is the same as LIMIT 10 OFFSET 5.

How do you get page N of results with page size K?

ORDER BY unique_key LIMIT K OFFSET (N - 1) * K. For deep pages, keyset pagination (WHERE key > last_seen ORDER BY key LIMIT K) is faster.

Why is ORDER BY salary DESC LIMIT 1 OFFSET N-1 not always correct for the Nth highest salary?

OFFSET counts rows, so duplicate salaries shift the position. Use SELECT DISTINCT salary ... LIMIT 1 OFFSET N-1, a subquery, or DENSE_RANK().

What is the SQL Server / standard equivalent of LIMIT?

SQL Server uses TOP n; standard SQL uses FETCH FIRST n ROWS ONLY (with OFFSET m ROWS). MySQL supports only LIMIT.


Summary

  • Only ORDER BY guarantees row order. Default direction is ASC; use DESC for largest/newest first.

  • Multiple sort keys are applied left to right; each has its own direction.

  • Add a unique tie-breaker when tied rows must have a stable order.

  • ORDER BY can use aliases, expressions, unselected columns and (avoid in real code) column positions.

  • MySQL sorts NULLs first ascending; use col IS NULL, col to move them last.

  • LIMIT n keeps the first n rows after sorting; OFFSET m skips m rows. LIMIT m, n means skip m, take n.

  • Pagination: LIMIT size OFFSET (page − 1) × size; use keyset pagination for deep pages.

  • LIMIT counts rows, not distinct values, which matters for "Nth highest" questions.

Next, in the DISTINCT chapter, we will remove duplicate values from results and use it to solve the Nth highest salary problem properly.