ORDER BY and LIMIT

LIMIT is supported by MySQL, PostgreSQL, and SQLite; other databases use alternatives. Without a deterministic ORDER BY (including a tie-breaker when needed), a top-N result is not stable.

1. Introduction

Once SQL has selected and filtered your data, the next step is deciding:

• In what order should the rows appear?
• How many rows should be shown?

This is where ORDER BY and LIMIT become important.

These two clauses control how your final result is presented.

They do not change the data stored in the database.

They only change how the result is shown to you.

This is extremely useful in real-world SQL because raw data is often too large and too messy to read directly.

2. What is ORDER BY?

ORDER BY is used to sort the rows of a result set.

It controls the order in which rows appear in the final output.

You can sort by:

• numbers
• text
• dates
• multiple columns
• calculated values

Simple Definition

ORDER BY decides:

In what order should I see the rows?

3. What is LIMIT?

LIMIT is used to restrict the number of rows returned.

It tells SQL to return only a fixed number of rows from the top of the result.

This is useful when:

• you only need a few rows
• you want top results
• you want previews
• you want leaderboards
• you want recent records

Simple Definition

LIMIT decides:

How many rows should SQL return?

4. Basic Example

Find the top 2 countries with the greatest land area.

SELECT country, area
FROM countries
ORDER BY area DESC
LIMIT 2;

Output

Explanation

SQL performs this in order:

1. Read rows
2. Sort by area (largest first)
3. Return only top 2 rows

5. Deep Dive — Organizing Your Results

Imagine a list of 1,000 customers.

If the rows are random, the list is hard to read.

But if sorted:

• alphabetically
• by revenue
• by signup date
• by order count

the data becomes much more useful.

This is what ORDER BY does.

It organizes the output.

Important Note

ORDER BY changes only the result view.

It does not change the actual stored table order.

The database remains unchanged.

Only the displayed output is sorted.

6. Why LIMIT Matters

Large datasets can return thousands of rows.

Most of the time, you do not need all of them.

LIMIT helps you:

• reduce noise
• focus on top rows
• speed up reading
• create summaries

It is heavily used in:

• Top 10 lists
• dashboards
• leaderboards
• recent orders
• pagination

7. Real-World Example — products Table

We will use the products table.

8. ORDER BY ASC (Ascending)

Definition

Ascending means:

• smallest to largest
• A to Z
• oldest to newest

ASC is the default sort order.

Even if you do not write ASC, SQL uses it automatically.

Query

SELECT product_name, price
FROM products
ORDER BY price;

Explanation

Sort products by price from lowest to highest.

Since ASC is default, SQL assumes:

ORDER BY price ASC

Output

9. ORDER BY DESC (Descending)

Definition

Descending means:

• largest to smallest
• Z to A
• newest to oldest

Use DESC when you want highest values first.

Query

SELECT product_name, stock_quantity
FROM products
ORDER BY stock_quantity DESC;

Explanation

Sort products by stock quantity from highest to lowest.

Output

10. Top N Analysis (ORDER BY + LIMIT)

This is one of the most common SQL patterns.

To find “Top N” rows:

1. Sort in descending order
2. Limit the result

Query

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 2;

Explanation

1. Sort by highest price
2. Return only top 2 rows

Output

Common Use Cases

Used for:

• top 10 customers
• top 5 products
• highest salaries
• latest orders
• top-performing users

11. Multi-Level Sorting

You can sort by more than one column.

SQL sorts by the first column first.

If two rows tie, SQL uses the second column as a tie-breaker.

Query

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

Explanation

Step 1: Sort by category (A to Z)

Step 2: Inside each category, sort by price (high to low)

Output

Why This Matters

Multi-level sorting is used in:

• grouped reports
• category dashboards
• ranked lists
• organized exports

12. Sorting by Calculated Values

You can also sort by calculated columns.

This is very useful in reporting.

Query

SELECT product_name, price * stock_quantity AS total_value
FROM products
ORDER BY total_value DESC;

Explanation

SQL calculates total_value first, then sorts using that alias.

This is valid and commonly used.

13. Common Mistakes to Avoid

1. Wrong Placement of LIMIT

Incorrect:

SELECT product_name
FROM products
LIMIT 2
ORDER BY price DESC;

Correct:

SELECT product_name
FROM products
ORDER BY price DESC
LIMIT 2;

LIMIT must come last.

2. Forgetting Default ASC

If you do not write ASC, SQL still uses ascending order.

Always remember:

ORDER BY price

means:

ORDER BY price ASC

3. Confusing Text Sorting

For text:

• ASC = A to Z
• DESC = Z to A

This matters for names, categories, cities, etc.

4. Forgetting Tie-Breakers

When sorting by one column, ties may appear random.

Use a second column for stable ordering.

14. Summary

ORDER BY sorts rows.

LIMIT restricts row count.

Together, they help SQL present results in a clean and useful way.

They are essential for:

• top lists
• sorted reports
• dashboards
• previews
• rankings

Final Mental Model

ORDER BY answers:

In what order should SQL show the rows?

LIMIT answers:

How many rows should SQL return?