06. Sorting & Limiting Results -- ORDER BY & TOP

Level: Beginner

ℹ️ What You'll Learn

• ORDER BY clause -- sort results ascending or descending
• Sort by single column
• Sort by multiple columns
• TOP clause -- limit number of rows returned
• TOP with PERCENT -- get percentage of results
• OFFSET and FETCH -- pagination
• Common sorting mistakes
• Performance impact of sorting

SELECT retrieves data. ORDER BY sorts it. TOP limits it. This article teaches sorting and limiting -- essential for APIs returning paginated results.

Why ORDER BY & TOP Matter for Backend Developers

APIs need sorted, paginated results:

Frontend requests: "Give me top 10 students sorted by roll number"
    v
C# Backend API receives: GET /students?sort=roll&limit=10
    v
Backend calls: SELECT TOP 10 * FROM Student ORDER BY RollNumber
    v
SQL Server retrieves 10 students, sorted by roll
    v
Backend returns sorted, limited data (JSON)

Without ORDER BY, results come in random order. Without TOP, large tables return thousands of rows.

ORDER BY Syntax

SELECT ColumnList FROM TableName
WHERE Condition
ORDER BY Column1 ASC, Column2 DESC;

Parts:

• ORDER BY -- Sort results
• ASC -- Ascending (A to Z, 0 to 9) -- default
• DESC -- Descending (Z to A, 9 to 0)

Example 1: ORDER BY Single Column (Ascending)

Sort students by name A to Z:

SELECT * FROM Student ORDER BY Name ASC;

Result:

StudentId | Name         | RollNumber | ClassName | Section
3         | Arjun Reddy  | 103        | 10-B      | B
2         | Priya Sharma | 102        | 10-A      | A
1         | Ravi Kumar   | 101        | 10-A      | A

Names alphabetically: Arjun -> Priya -> Ravi.

Example 2: ORDER BY Single Column (Descending)

Sort students by roll number Z to A (highest first):

SELECT * FROM Student ORDER BY RollNumber DESC;

Result:

StudentId | Name        | RollNumber | ClassName | Section
3         | Arjun Reddy | 103        | 10-B      | B
2         | Priya Sharma| 102        | 10-A      | A
1         | Ravi Kumar  | 101        | 10-A      | A

Rolls descending: 103 -> 102 -> 101.

Example 3: ORDER BY Without Specifying ASC/DESC

Default is ASC (ascending):

SELECT * FROM Student ORDER BY RollNumber;

Same as: ORDER BY RollNumber ASC.

Result:

StudentId | Name         | RollNumber | ClassName | Section
1         | Ravi Kumar   | 101        | 10-A      | A
2         | Priya Sharma | 102        | 10-A      | A
3         | Arjun Reddy  | 103        | 10-B      | B

Example 4: ORDER BY Multiple Columns

Sort by class, then by roll within each class:

SELECT * FROM Student 
ORDER BY ClassName ASC, RollNumber ASC;

Result:

StudentId | Name         | RollNumber | ClassName | Section
1         | Ravi Kumar   | 101        | 10-A      | A
2         | Priya Sharma | 102        | 10-A      | A
3         | Arjun Reddy  | 103        | 10-B      | B

First sort by ClassName (10-A before 10-B), then within each class by RollNumber.

Example 5: ORDER BY With Different Directions

Sort by class ascending, then by name descending within class:

SELECT * FROM Student 
ORDER BY ClassName ASC, Name DESC;

Result:

StudentId | Name         | RollNumber | ClassName | Section
2         | Priya Sharma | 102        | 10-A      | A
1         | Ravi Kumar   | 101        | 10-A      | A
3         | Arjun Reddy  | 103        | 10-B      | B

Within 10-A: Priya before Ravi (descending names). Within 10-B: Only Arjun.

TOP Clause

Return only first N rows:

SELECT TOP 10 * FROM Student;

Returns 10 students (or fewer if table has less).

Example 6: TOP With ORDER BY

Get top 5 highest roll numbers:

SELECT TOP 5 * FROM Student 
ORDER BY RollNumber DESC;

Result (if more students exist):

StudentId | Name         | RollNumber | ClassName
...       | ...          | 105        | ...
...       | ...          | 104        | ...
...       | ...          | 103        | 10-B
2         | Priya Sharma | 102        | 10-A
1         | Ravi Kumar   | 101        | 10-A

First sorts all by RollNumber DESC, then returns top 5.

Example 7: TOP PERCENT

Get top 50% of students by roll number:

SELECT TOP 50 PERCENT * FROM Student 
ORDER BY RollNumber DESC;

If 100 students: returns top 50. If 10 students: returns top 5.

OFFSET and FETCH (Pagination)

For large result sets, use OFFSET/FETCH instead of TOP:

SELECT * FROM Student
ORDER BY RollNumber
OFFSET 10 ROWS          -- Skip first 10
FETCH NEXT 5 ROWS ONLY; -- Get next 5

Gets rows 11-15.

Example 8: Pagination (Page 1)

Get first 10 students:

SELECT * FROM Student
ORDER BY RollNumber
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Example 9: Pagination (Page 2)

Get next 10 students (rows 11-20):

SELECT * FROM Student
ORDER BY RollNumber
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Formula: OFFSET = (PageNumber - 1) * PageSize

Querying Teacher Table

Get all teachers sorted by salary (highest first):

SELECT Name, Salary FROM Teacher 
ORDER BY Salary DESC;

Result:

Name        | Salary
Mrs. Rao    | 55000.0
Dr. Mehta   | 50000.0
Mr. Sharma  | 45000.0

Get top 2 highest paid teachers:

SELECT TOP 2 Name, Salary FROM Teacher 
ORDER BY Salary DESC;

Result:

Name     | Salary
Mrs. Rao | 55000.0
Dr. Mehta| 50000.0

---

Common ORDER BY & TOP Mistakes

Mistake 1: Using ORDER BY without TOP for "top N" query

Wrong:

-- Intended: Get top 3 students by roll
SELECT * FROM Student ORDER BY RollNumber DESC;
-- Returns ALL students sorted (could be 10,000 rows)

Returns entire table sorted. Backend has to limit in code.

Result: Slow query, transfers huge dataset to backend, wastes bandwidth.

Fix: Use TOP:

SELECT TOP 3 * FROM Student ORDER BY RollNumber DESC;
-- Returns only 3 rows

Mistake 2: Sorting by column number instead of column name

Wrong (SQL Server doesn't support this):

SELECT * FROM Student ORDER BY 1, 2;
-- Error or confusing behavior

Result: Confusing. Hard to maintain. Don't do this.

Fix: Always use column names:

SELECT * FROM Student ORDER BY Name, RollNumber;

Mistake 3: Using ORDER BY without columns in SELECT

Works but confusing:

SELECT Name FROM Student ORDER BY RollNumber;

Sorts by RollNumber but doesn't return it. Reader doesn't see sort key.

Result: Confusing for maintenance.

Fix: Include sort column in SELECT:

SELECT Name, RollNumber FROM Student ORDER BY RollNumber;

Now clear why sorted.

Mistake 4: Forgetting OFFSET 0 in pagination

Wrong:

-- Page 1
SELECT * FROM Student
FETCH NEXT 10 ROWS ONLY;
-- Error: OFFSET required before FETCH

FETCH requires OFFSET.

Result: Syntax error.

Fix: Always include OFFSET (even if 0):

SELECT * FROM Student
ORDER BY RollNumber
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

---

Best Practices for ORDER BY & TOP

1. Always use ORDER BY before TOP -- TOP without ORDER BY returns unpredictable rows
2. Use column names, not numbers -- Clearer, maintainable code
3. Include sort column in SELECT -- Shows reader why sorted
4. Use OFFSET/FETCH for pagination -- Better than TOP for large datasets
5. Index sort columns -- ORDER BY on indexed columns is faster
6. Limit results with TOP -- Never return all rows to backend
7. Test with EXPLAIN PLAN -- Verify sort doesn't cause full table scan

---

🎯 Q1: What's the difference between ASC and DESC?

ASC (Ascending) -- A to Z, 0 to 9, earliest to latest

SELECT * FROM Student ORDER BY Name ASC;
-- Result: Arjun, Priya, Ravi (alphabetically)

DESC (Descending) -- Z to A, 9 to 0, latest to earliest

SELECT * FROM Student ORDER BY Name DESC;
-- Result: Ravi, Priya, Arjun (reverse alphabetically)

Default: ASC (if you don't specify, it's ascending)

Use:

• ASC for alphabetical, chronological, lowest-first
• DESC for reverse alphabetical, newest-first, highest-first (leaderboards, rankings)

🎯 Q2: How do I sort by multiple columns?

SELECT * FROM Student 
ORDER BY ClassName ASC, RollNumber DESC;

Order matters:

1. First sorts by ClassName (10-A before 10-B)
2. Within each class, sorts by RollNumber (highest first)

Example result:

ClassName | RollNumber
10-A      | 102
10-A      | 101
10-B      | 103

Each column can be ASC or DESC independently.

🎯 Q3: What's the difference between TOP and OFFSET/FETCH?

TOP: Simple, returns first N rows

SELECT TOP 10 * FROM Student ORDER BY RollNumber;

Good for: "Get top 10 students"

OFFSET/FETCH: Pagination support

SELECT * FROM Student 
ORDER BY RollNumber
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Good for: Pages (skip 10, get next 10 = page 2)

Difference:

• TOP: Always gets first N
• OFFSET/FETCH: Can skip and get, enabling pagination

For APIs: Use OFFSET/FETCH for pagination support.

🎯 Q4: How do I implement pagination in my API?

Backend receives: GET /students?page=2&pageSize=10

Calculate OFFSET:

OFFSET = (page - 1) * pageSize
OFFSET = (2 - 1) * 10 = 10

SQL Query:

SELECT * FROM Student
ORDER BY RollNumber
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

C# example:

int page = 2, pageSize = 10;
int offset = (page - 1) * pageSize;
var students = ExecuteQuery(
  $"SELECT * FROM Student ORDER BY RollNumber OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY"
);

Returns: Page 2 (rows 11-20)

🎯 Q5: Do I need to use ORDER BY before TOP?

Short answer: Yes, almost always.

Without ORDER BY:

SELECT TOP 5 * FROM Student;
-- Returns 5 students in random/storage order

Random. Unpredictable. Bad for leaderboards, rankings, "top" anything.

With ORDER BY:

SELECT TOP 5 * FROM Student ORDER BY RollNumber DESC;
-- Returns 5 highest roll numbers

Predictable. Correct. Used for leaderboards, "top sales", "top scores".

Rule: If you're asking for "top", you want ORDER BY first.

🎯 Q6: Why is ORDER BY slow on large tables?

Large table (1M+ rows):

Sorting all 1M rows takes time. If no index, SQL Server must:

1. Read all rows into memory
2. Sort entire dataset
3. Return results

Solution -- Use Indexes:

CREATE INDEX IX_Student_RollNumber ON Student(RollNumber);

Query becomes fast because index is already sorted.

Rule: If you ORDER BY a column frequently, create an index on it.

For APIs: Always test queries on realistic data sizes. 10 rows sorts instant. 1M rows without index = slow.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on ORDER BY and TOP clauses in SQL Server. Try these prompts:

• "Explain ORDER BY ascending/descending with Student table examples"
• "How do I sort by multiple columns with different ASC/DESC directions?"
• "What's the difference between TOP and OFFSET/FETCH? When use each?"
• "Walk me through implementing pagination in a backend API"
• "Quiz me: ask 5 questions about ORDER BY, TOP, sorting, pagination"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

07. Modifying Data -- UPDATE & DELETE ->