SQL WHERE, ORDER BY & LIMIT Explained Simply (With Examples)

When I first learned SQL, SELECT * FROM orders felt like magic. Then I ran it on a real table with 80,000 rows and my screen filled with a wall of data I couldn’t use. That’s when I realised the useful part of SQL isn’t getting data out. It’s getting the right data out.

Three clauses do most of that work: WHERE, ORDER BY and LIMIT. Once you’re comfortable with them, you can answer most everyday questions about your data in one short query. If you haven’t written a query before, start with SQL for Complete Beginners and come back here.

Diagram showing how WHERE, ORDER BY and LIMIT narrow down a table

The Sample Table We’ll Use

To keep things concrete, every example below uses a small orders table from an imaginary online bookshop:

order_idcustomercityamountorder_date
1SaraManama25.002026-09-01
2OmarRiffa80.502026-09-03
3LinaManama12.752026-09-04
4YousefMuharraq150.002026-09-10
5HudaManama64.202026-09-12
6AliRiffa9.992026-09-15

Six rows is tiny, but the queries work exactly the same on six million.

WHERE: Keep Only the Rows You Care About

WHERE is a filter. SQL checks every row against your condition and only keeps the ones where the condition is true.

SELECT customer, amount
FROM orders
WHERE city = 'Manama';

This returns Sara, Lina and Huda. Everyone else is dropped before you ever see them.

Comparison operators you’ll use all the time

OperatorMeaningExample
=Equal tocity = 'Riffa'
<> or !=Not equal tocity <> 'Riffa'
> / <Greater / less thanamount > 50
>= / <=Greater / less than or equalamount <= 25
BETWEENInside a range (inclusive)amount BETWEEN 20 AND 100
INMatches any value in a listcity IN ('Riffa','Muharraq')
LIKEMatches a text patterncustomer LIKE 'S%'
IS NULLHas no valuecity IS NULL

Combining conditions with AND and OR

Real questions usually have more than one condition. “Show me big orders from Manama” becomes:

SELECT customer, amount
FROM orders
WHERE city = 'Manama'
  AND amount > 20;

That gives Sara (25.00) and Huda (64.20). Lina’s order is from Manama, but 12.75 doesn’t pass the second check.

Here’s a mistake I made more than once. Say you want orders over 50 from either Riffa or Muharraq:

-- Wrong: AND is evaluated before OR
WHERE city = 'Riffa' OR city = 'Muharraq' AND amount > 50

-- Right: brackets make your intent clear
WHERE (city = 'Riffa' OR city = 'Muharraq') AND amount > 50

Without brackets, SQL reads the first version as “any Riffa order, or a Muharraq order over 50”, so Ali’s 9.99 order sneaks in. When you mix AND and OR, just use brackets. It costs nothing and saves you from confusing results.

A quick word on NULL

NULL means “unknown”, and it doesn’t behave like a normal value. WHERE city = NULL returns nothing, ever. You have to write WHERE city IS NULL (or IS NOT NULL). Almost everyone trips on this at least once.

ORDER BY: Put the Results in a Useful Order

Without ORDER BY, the database returns rows in whatever order is convenient for it. That order can change, so never rely on it. If the order matters, ask for it:

SELECT customer, amount
FROM orders
ORDER BY amount DESC;

DESC means largest first. ASC (smallest first) is the default, so you can leave it out. Yousef’s 150.00 order comes out on top and Ali’s 9.99 is at the bottom.

Sorting by more than one column

You can sort by several columns. SQL sorts by the first one, then uses the next to break ties:

SELECT city, customer, amount
FROM orders
ORDER BY city ASC, amount DESC;

Now cities are listed alphabetically, and inside each city the biggest order comes first. This is handy for reports where you want groups that are easy to scan.

LIMIT: Only Take the First Few Rows

LIMIT tells the database to stop after a certain number of rows. On its own it isn’t very meaningful, but paired with ORDER BY it answers “top N” questions:

-- Top 3 biggest orders
SELECT customer, amount
FROM orders
ORDER BY amount DESC
LIMIT 3;

Result: Yousef (150.00), Omar (80.50), Huda (64.20).

One catch: not every database spells it LIMIT. MySQL, PostgreSQL and SQLite do. SQL Server uses TOP, and newer standard SQL uses FETCH FIRST:

DatabaseSyntax for “top 3”
MySQL, PostgreSQL, SQLite... ORDER BY amount DESC LIMIT 3
SQL ServerSELECT TOP 3 ... ORDER BY amount DESC
Oracle, PostgreSQL, SQL Server 2012+... ORDER BY amount DESC OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY

If you work with SQL Server a lot, like I do, you’ll be typing TOP far more often than LIMIT.

Paging with OFFSET

OFFSET skips rows before LIMIT starts counting. It’s how websites show “page 2” of results:

-- Rows 4 to 6 (page 2, 3 per page)
SELECT customer, amount
FROM orders
ORDER BY amount DESC
LIMIT 3 OFFSET 3;

ORDER BY and LIMIT: Three Details That Matter

For the next examples, imagine one more order arrives: order 7, from Mona, for 64.20, with no city recorded (NULL). Now Huda and Mona have the same amount.

1. Ties: who gets the last place?

“Top 3 orders” is now ambiguous: Yousef and Omar are clearly first and second, but Huda and Mona tie for third. LIMIT 3 returns one of them, and which one is not guaranteed. It can even change between runs.

If you want both, ask for ties explicitly:

-- SQL Server
SELECT TOP (3) WITH TIES customer, amount
FROM orders
ORDER BY amount DESC;

-- PostgreSQL 13+, Oracle (standard SQL)
SELECT customer, amount
FROM orders
ORDER BY amount DESC
FETCH FIRST 3 ROWS WITH TIES;
-- Yousef 150.00, Omar 80.50, Huda 64.20, Mona 64.20

MySQL has no WITH TIES; use a window function such as RANK() instead (see Top N per Group in SQL Server for the pattern). If you want exactly 3 rows, add a tie-breaker so the result is always the same: ORDER BY amount DESC, order_id.

2. Where do NULLs go?

Sorting by city puts Mona’s NULL city at a different end depending on the database:

DatabaseNULLs in ORDER BY … ASCTo change it
SQL Server, MySQLFirstSort by a CASE first, e.g. ORDER BY CASE WHEN city IS NULL THEN 1 ELSE 0 END, city
PostgreSQL, OracleLastORDER BY city NULLS FIRST

With DESC, everything flips. If the position of empty values matters in a report, say so explicitly rather than relying on the default.

3. Paging needs a unique sort order

When you page with LIMIT ... OFFSET, the sort order must be unique, or rows can appear on two pages, or on none. Sorting by amount alone isn’t unique (two orders have 64.20), so always add the primary key at the end:

SELECT order_id, customer, amount
FROM orders
ORDER BY amount DESC, order_id DESC
LIMIT 4 OFFSET 0;   -- page 1: Yousef, Omar, Mona, Huda

On very large tables, deep pages (OFFSET 100000) get slow, because the database still has to read and throw away all the skipped rows. The faster alternative is “keyset” paging: remember the last row of the page and continue from it.

-- Page 2: continue after the last row of page 1 (amount 64.20, order_id 5)
SELECT order_id, customer, amount
FROM orders
WHERE amount < 64.20
   OR (amount = 64.20 AND order_id < 5)
ORDER BY amount DESC, order_id DESC
LIMIT 4;            -- Sara, Lina, Ali

WHERE, ORDER BY and LIMIT in Real Projects

In a business application, these three clauses sit behind almost every list on the screen: the last ten invoices, today’s orders, the top customers this month. A few habits keep those screens fast as the tables grow.

“Latest N” screens need the right index

A customer screen that shows the ten most recent orders runs a query like this every time it opens:

-- SQL Server
SELECT TOP (10) order_id, order_date, amount
FROM orders
WHERE customer_id = @CustomerID
ORDER BY order_date DESC;

With an index on (customer_id, order_date), the database jumps to that customer, reads the newest 10 entries in order and stops. Without it, it may read every order the customer ever placed, sort them all, and then throw most away. The index makes the difference between 10 rows read and 100,000. See SQL Indexes Explained Simply.

Filter dates with a range, not a function

This looks natural but stops the database from using an index on order_date, because it has to calculate YEAR() for every row first:

-- Slow on big tables
WHERE YEAR(order_date) = 2026

-- Fast: the same rows, and an index on order_date can be used
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01'

The same applies to MONTH(), LEFT() and other functions wrapped around a column in WHERE. More patterns are in SQL Date Functions Explained Simply.

Pass filter values as parameters

When the city or date comes from a search box, never paste the user’s text into the SQL string. Use parameters (@City in SQL Server, :city in Delphi’s FireDAC, ? or named bindings in Laravel). It protects you from SQL injection and lets the database reuse the plan.

Putting All Three Together

Here’s a query you might actually write at work: “Which two Manama orders this month were the largest?”

SELECT customer, amount, order_date
FROM orders
WHERE city = 'Manama'
  AND order_date >= '2026-09-01'
ORDER BY amount DESC
LIMIT 2;

Huda (64.20) and Sara (25.00). Short, readable, and it would work just as well on a table with millions of rows.

The Order SQL Actually Runs Things In

You write the clauses in one order, but the database processes them in another. Knowing this explains a lot of error messages:

  1. FROM picks the table
  2. WHERE filters the rows
  3. SELECT picks the columns
  4. ORDER BY sorts what’s left
  5. LIMIT cuts it down to size

This is why you can’t use a column alias from SELECT inside WHERE in most databases: at the time WHERE runs, the alias doesn’t exist yet. ORDER BY runs later, so it can use the alias.

Common Mistakes to Avoid

  • Using LIMIT without ORDER BY and expecting the “top” rows. You’ll just get some rows.
  • Writing = NULL instead of IS NULL.
  • Mixing AND and OR without brackets.
  • Putting quotes around numbers (amount > '50'). It often works, but it can force slow conversions or odd text comparisons.
  • Forgetting that text comparisons may be case-sensitive depending on the database settings.

Frequently Asked Questions

Does LIMIT go before or after ORDER BY?

After. The order is SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT. The database sorts first and then takes the first N rows, which is why ORDER BY amount DESC LIMIT 3 gives the three biggest orders.

Can I use LIMIT in SQL Server?

No. SQL Server uses SELECT TOP (n), or OFFSET ... ROWS FETCH NEXT n ROWS ONLY after ORDER BY (SQL Server 2012 and later), which also supports paging.

Can I use ORDER BY on a column that isn’t in the SELECT list?

Yes, in most cases: SELECT customer FROM orders ORDER BY amount DESC works. The exception is SELECT DISTINCT, where the sort column must be in the select list.

Does ORDER BY slow a query down?

Sorting costs time on large results. If an index already stores the rows in the order you ask for, the database can read them in order and skip the sort, which is why “latest N” queries need a matching index. Only use ORDER BY when the order matters; don’t add it to queries whose results you sort elsewhere.

Conclusion

WHERE decides which rows you keep, ORDER BY decides how they’re arranged, and LIMIT decides how many you see. Between them, they turn a huge table into a short, useful answer.

Try writing a few queries of your own on any table you have access to. Once these feel natural, the next step is summarising data with GROUP BY and aggregate functions, and then pulling data from more than one table with SQL JOINs.

Related articles

Scroll to Top