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.

Table of Contents
The Sample Table We’ll Use
To keep things concrete, every example below uses a small orders table from an imaginary online bookshop:
| order_id | customer | city | amount | order_date |
|---|---|---|---|---|
| 1 | Sara | Manama | 25.00 | 2026-09-01 |
| 2 | Omar | Riffa | 80.50 | 2026-09-03 |
| 3 | Lina | Manama | 12.75 | 2026-09-04 |
| 4 | Yousef | Muharraq | 150.00 | 2026-09-10 |
| 5 | Huda | Manama | 64.20 | 2026-09-12 |
| 6 | Ali | Riffa | 9.99 | 2026-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
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | city = 'Riffa' |
<> or != | Not equal to | city <> 'Riffa' |
> / < | Greater / less than | amount > 50 |
>= / <= | Greater / less than or equal | amount <= 25 |
BETWEEN | Inside a range (inclusive) | amount BETWEEN 20 AND 100 |
IN | Matches any value in a list | city IN ('Riffa','Muharraq') |
LIKE | Matches a text pattern | customer LIKE 'S%' |
IS NULL | Has no value | city 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:
| Database | Syntax for “top 3” |
|---|---|
| MySQL, PostgreSQL, SQLite | ... ORDER BY amount DESC LIMIT 3 |
| SQL Server | SELECT 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:
| Database | NULLs in ORDER BY … ASC | To change it |
|---|---|---|
| SQL Server, MySQL | First | Sort by a CASE first, e.g. ORDER BY CASE WHEN city IS NULL THEN 1 ELSE 0 END, city |
| PostgreSQL, Oracle | Last | ORDER 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:
FROMpicks the tableWHEREfilters the rowsSELECTpicks the columnsORDER BYsorts what’s leftLIMITcuts 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
LIMITwithoutORDER BYand expecting the “top” rows. You’ll just get some rows. - Writing
= NULLinstead ofIS NULL. - Mixing
ANDandORwithout 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
- NULL in SQL Explained Simply: IS NULL, COALESCE and the common traps.
- SQL Indexes Explained Simply: what indexes are and when to add one.
- The SQL CASE Expression: if/then logic inside a query.
- The 10 Best SQL and Database Books: our picks from beginner to advanced.
- SQL LIKE, IN and BETWEEN Explained Simply: search text, match lists and filter ranges.
- SQL DISTINCT and Column Aliases Explained Simply: remove duplicate rows and give columns readable names.