🎓 Velnov

🗄️ SQL · Topic 4 of 20

Sorting & Limiting — SQL Questions with Answers

30 practice questions on Sorting & Limiting, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

2 easy · 18 medium · 10 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

ORDER BYSortingASCDESCMulti-column sortGuaranteesDISTINCTAliasesExecution orderPositional sortBest practicesLIMITTOPDialectsOFFSETPaginationPerformanceKeyset paginationNULLCASECustom sortingData typesCASTMAX

Jump to: Easy · Medium · Hard

Easy questions (2)

Q1. easy

Which clause sorts the result rows?

SELECT name, marks FROM students ORDER BY marks;
  1. A ORDER BY
  2. B SORT BY
  3. C GROUP BY
  4. D ARRANGE BY

Answer: A — ORDER BY

ORDER BY sorts the final result. GROUP BY groups rows for aggregation and does NOT guarantee any ordering.

ORDER BYSorting

Q2. easy

What is the DEFAULT sort direction?

ORDER BY marks DESC;   -- highest first
  1. A ASC - ascending (smallest first, A to Z)
  2. B DESC - descending
  3. C Random
  4. D Insertion order

Answer: A — ASC - ascending (smallest first, A to Z)

ORDER BY marks is the same as ORDER BY marks ASC. Write DESC explicitly for highest-first.

ASCDESC

Medium questions (18)

Q3. medium

How do you sort by class ascending, then by marks descending within each class?

  1. A ORDER BY class ASC, marks DESC
  2. B ORDER BY class, marks ASC DESC
  3. C ORDER BY class AND marks DESC
  4. D ORDER BY class DESC marks

Answer: A — ORDER BY class ASC, marks DESC

List columns in priority order, each with its own direction. The second column only breaks ties within equal values of the first.

Multi-column sort

Q5. medium

Can you sort by a column that is NOT in the SELECT list?

  1. A Yes - ORDER BY can use any column from the source table
  2. B No, it must be selected
  3. C Only with an alias
  4. D Only for numbers

Answer: A — Yes - ORDER BY can use any column from the source table

SELECT name FROM students ORDER BY marks works fine. (The exception is with SELECT DISTINCT or UNION, where the sort column must appear in the result.)

ORDER BYDISTINCT

Q6. medium

Can ORDER BY use a column ALIAS defined in SELECT?

SELECT price * qty AS total
FROM items
ORDER BY total DESC;   -- alias works here
  1. A Yes - ORDER BY runs AFTER SELECT, so the alias exists
  2. B No, aliases never work there
  3. C Only in MySQL
  4. D Only for computed columns

Answer: A — Yes - ORDER BY runs AFTER SELECT, so the alias exists

This is the mirror of the WHERE rule: WHERE runs before SELECT (no alias), ORDER BY runs after (alias available).

AliasesExecution order

Q8. medium

Which clause limits how many rows are returned in MySQL/PostgreSQL?

-- MySQL / PostgreSQL
SELECT * FROM students ORDER BY marks DESC LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM students ORDER BY marks DESC;
  1. A LIMIT 10
  2. B TOP 10
  3. C MAX 10
  4. D FIRST 10

Answer: A — LIMIT 10

MySQL, PostgreSQL and SQLite use LIMIT. SQL Server uses SELECT TOP 10, and the SQL standard is FETCH FIRST 10 ROWS ONLY.

LIMITTOPDialects

Q10. medium

What does OFFSET do? LIMIT 10 OFFSET 20

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;   -- page 3
  1. A Skips the first 20 rows, then returns the next 10 - used for pagination
  2. B Returns rows 10 to 20
  3. C Skips 10 rows
  4. D Returns 30 rows

Answer: A — Skips the first 20 rows, then returns the next 10 - used for pagination

For page N with a page size of 10: OFFSET = (N - 1) * 10. Page 3 skips 20 rows.

OFFSETPagination

Q12. medium

Where do NULLs appear when sorting ascending?

ORDER BY score DESC NULLS LAST;   -- PostgreSQL/Oracle
  1. A It depends on the database - PostgreSQL puts them LAST, MySQL puts them FIRST
  2. B Always first
  3. C Always last
  4. D They are excluded

Answer: A — It depends on the database - PostgreSQL puts them LAST, MySQL puts them FIRST

For portable, explicit control use NULLS FIRST / NULLS LAST where supported, or sort on a CASE expression that maps NULLs to a chosen position.

NULLSorting

Q14. medium

How do numbers stored as TEXT sort?

  1. A Alphabetically - so '10' comes before '9'
  2. B Numerically
  3. C Randomly
  4. D They cannot be sorted

Answer: A — Alphabetically - so '10' comes before '9'

Character-by-character comparison puts '10' before '9' because '1' < '9'. Cast to a number to fix it: ORDER BY CAST(code AS INT). Better still, store numbers in numeric columns.

Data typesCAST

Q15. medium

How do you find the single highest-scoring student?

  1. A SELECT * FROM students ORDER BY marks DESC LIMIT 1;
  2. B SELECT MAX(*) FROM students;
  3. C SELECT TOP marks FROM students;
  4. D SELECT FIRST(marks) FROM students;

Answer: A — SELECT * FROM students ORDER BY marks DESC LIMIT 1;

Sorting descending and taking one row also gives you the whole row, not just the maximum value (which is all MAX(marks) would return).

LIMITMAX

Q17. medium

How do you get a random row?

  1. A ORDER BY RANDOM() LIMIT 1 (RAND() in MySQL, NEWID() in SQL Server)
  2. B SELECT RANDOM FROM t
  3. C LIMIT RANDOM
  4. D It is not possible

Answer: A — ORDER BY RANDOM() LIMIT 1 (RAND() in MySQL, NEWID() in SQL Server)

It works but is expensive on big tables because every row gets a random value and the whole set is sorted. For large data, sample by id range instead.

RandomPerformance

Q18. medium

Does ORDER BY affect which rows are returned?

  1. A No - it only changes their ORDER (unless combined with LIMIT)
  2. B Yes, it filters rows
  3. C Yes, it removes duplicates
  4. D It groups rows

Answer: A — No - it only changes their ORDER (unless combined with LIMIT)

Filtering is WHERE's job. But with LIMIT, the order determines WHICH rows survive the cut - which is why the pair must be considered together.

ORDER BYLIMIT

Q20. medium

How do you sort case-insensitively?

  1. A ORDER BY LOWER(name)
  2. B ORDER BY name IGNORE CASE
  3. C ORDER BY name ASC ASC
  4. D It is automatic everywhere

Answer: A — ORDER BY LOWER(name)

Without it, some databases place all capitals before lowercase, so 'Zoe' sorts before 'anna'. Collation settings can also control this.

CollationLOWER

Q21. medium

How do you show the 5 most recent orders?

  1. A ORDER BY order_date DESC LIMIT 5
  2. B ORDER BY order_date ASC LIMIT 5
  3. C LIMIT 5 ORDER BY order_date
  4. D SELECT LAST 5 FROM orders

Answer: A — ORDER BY order_date DESC LIMIT 5

Most recent means largest date, so sort descending. Option B would give the five OLDEST orders.

DatesLIMIT

Q23. medium

Which is the SQL-standard way to limit rows?

SELECT * FROM t ORDER BY id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;
  1. A FETCH FIRST 10 ROWS ONLY
  2. B LIMIT 10
  3. C TOP 10
  4. D ROWNUM <= 10

Answer: A — FETCH FIRST 10 ROWS ONLY

OFFSET ... FETCH is the standard, supported by PostgreSQL, Oracle 12c+ and SQL Server. LIMIT is more common in practice; ROWNUM is old Oracle syntax.

StandardsDialects

Q24. medium

Can you use ORDER BY inside a subquery meaningfully?

  1. A Usually not - the outer query may reorder it; it only matters with LIMIT inside the subquery
  2. B Yes, order is always preserved
  3. C It is a syntax error
  4. D Only in views

Answer: A — Usually not - the outer query may reorder it; it only matters with LIMIT inside the subquery

Sorting a subquery is generally pointless (and some databases forbid it without LIMIT). Apply ORDER BY at the outermost level.

SubqueriesORDER BY

Q26. medium

Does ORDER BY slow down a query?

  1. A It can, since sorting takes work - but an index on the sort column can make it nearly free
  2. B Never
  3. C Always doubles the time
  4. D Only with LIMIT

Answer: A — It can, since sorting takes work - but an index on the sort column can make it nearly free

If an index already stores rows in the required order, the database can read them in order and skip sorting entirely - a big win for paginated queries.

IndexesPerformance

Q27. medium

How do you return rows 11-20 of a sorted list?

  1. A ORDER BY ... LIMIT 10 OFFSET 10
  2. B LIMIT 11, 20
  3. C BETWEEN 11 AND 20
  4. D OFFSET 20 LIMIT 10

Answer: A — ORDER BY ... LIMIT 10 OFFSET 10

Skip the first 10, then take 10. (MySQL also supports the shorthand LIMIT 10, 10 where the first number is the offset - easy to misread.)

PaginationOFFSET

Q29. medium

What is the safest ORDER BY for a paginated API?

  1. A A meaningful column plus a unique tiebreaker: ORDER BY created_at DESC, id DESC
  2. B ORDER BY created_at only
  3. C No ORDER BY, just LIMIT
  4. D ORDER BY RANDOM()

Answer: A — A meaningful column plus a unique tiebreaker: ORDER BY created_at DESC, id DESC

The unique column guarantees a total order, so pages never overlap or skip rows when timestamps are identical.

PaginationStability

Q30. medium

What does this query answer? SELECT city, COUNT(*) AS n FROM customers GROUP BY city ORDER BY n DESC LIMIT 3;

  1. A The three cities with the most customers
  2. B The first three cities alphabetically
  3. C Three random customers
  4. D It is invalid

Answer: A — The three cities with the most customers

Group to count per city, sort by that count descending, then keep the top 3 - the standard 'top N by category' report pattern.

GROUP BYTop N

Hard questions (10)

Q4. hard

Is the row order guaranteed WITHOUT an ORDER BY?

  1. A No - never rely on it, even if results look sorted; the database may return rows in any order
  2. B Yes, always insertion order
  3. C Yes, primary key order
  4. D Yes, alphabetical

Answer: A — No - never rely on it, even if results look sorted; the database may return rows in any order

Order can change with indexes, parallel execution or data growth. If order matters, say so explicitly with ORDER BY.

  • A classic source of bugs that appear only in production.

ORDER BYGuarantees

Q7. hard

What does ORDER BY 2 mean?

  1. A Sort by the SECOND column in the SELECT list (positional reference)
  2. B Sort by the value 2
  3. C Return 2 rows
  4. D A syntax error

Answer: A — Sort by the SECOND column in the SELECT list (positional reference)

It works, but it is fragile: changing the SELECT list silently changes the sort. Name the column or alias instead.

Positional sortBest practices

Q9. hard

Why must LIMIT almost always be paired with ORDER BY?

  1. A Without a defined order, 'the top 10' is arbitrary and can differ between runs
  2. B LIMIT is a syntax error alone
  3. C It is only for performance
  4. D They are unrelated

Answer: A — Without a defined order, 'the top 10' is arbitrary and can differ between runs

LIMIT truncates whatever order the database happened to produce. 'Top 10 scorers' only means something after ORDER BY marks DESC.

LIMITORDER BY

Q11. hard

Why does OFFSET become slow on very large tables?

  1. A The database must still read and discard all skipped rows - OFFSET 100000 scans 100,000 rows first
  2. B It locks the table
  3. C It uses no index at all
  4. D It is not slow

Answer: A — The database must still read and discard all skipped rows - OFFSET 100000 scans 100,000 rows first

For deep pagination, use keyset (cursor) pagination instead: remember the last row's sort value and filter WHERE id > last_id ORDER BY id LIMIT 10.

PaginationPerformanceKeyset pagination

Q13. hard

How would you sort so that a specific status appears first?

  1. A ORDER BY CASE WHEN status = 'urgent' THEN 0 ELSE 1 END, created_at
  2. B ORDER BY status = 'urgent'
  3. C ORDER BY urgent FIRST
  4. D It is impossible

Answer: A — ORDER BY CASE WHEN status = 'urgent' THEN 0 ELSE 1 END, created_at

A CASE expression creates a custom sort key. This 'priority sort' pattern is very common for dashboards and task lists.

CASECustom sorting

Q16. hard

What is a problem with ORDER BY marks DESC LIMIT 1 when there are TIES?

  1. A It arbitrarily picks ONE of the tied rows - the others are hidden
  2. B It errors on ties
  3. C It returns all tied rows
  4. D Ties are impossible

Answer: A — It arbitrarily picks ONE of the tied rows - the others are hidden

To return every top scorer, use a subquery (WHERE marks = (SELECT MAX(marks) ...)) or window functions with RANK(), which is covered in topic 20.

TiesRANK

Q19. hard

In which order does the database EXECUTE these clauses? SELECT / FROM / WHERE / ORDER BY / LIMIT

  1. A FROM, WHERE, SELECT, ORDER BY, LIMIT
  2. B SELECT, FROM, WHERE, ORDER BY, LIMIT
  3. C LIMIT, ORDER BY, WHERE, FROM, SELECT
  4. D The same order as written

Answer: A — FROM, WHERE, SELECT, ORDER BY, LIMIT

Data is read, filtered, projected, sorted and finally truncated. This explains why aliases work in ORDER BY but not WHERE, and why LIMIT applies to sorted output.

Execution order

Q22. hard

Why might the same LIMIT query return different rows on different runs?

  1. A Because ties (or a missing ORDER BY) leave the order undefined - add a unique tiebreaker column
  2. B Because LIMIT is random
  3. C Because of caching
  4. D It cannot happen

Answer: A — Because ties (or a missing ORDER BY) leave the order undefined - add a unique tiebreaker column

For stable pagination always end with a unique column: ORDER BY created_at DESC, id DESC. Otherwise rows can repeat or vanish between pages.

PaginationDeterministic order

Q25. hard

How do you sort by a value's position in a custom list?

ORDER BY CASE size
  WHEN 'S' THEN 1 WHEN 'M' THEN 2
  WHEN 'L' THEN 3 ELSE 4 END;
  1. A Use CASE (or FIELD() in MySQL) to map each value to a sort number
  2. B ORDER BY LIST(...)
  3. C ORDER BY IN (...)
  4. D It is impossible

Answer: A — Use CASE (or FIELD() in MySQL) to map each value to a sort number

For example ordering sizes S, M, L, XL - which are neither alphabetical nor numeric. Map them explicitly.

CASECustom order

Q28. hard

What does DISTINCT combined with ORDER BY require?

  1. A The ORDER BY columns must appear in the SELECT list
  2. B Nothing special
  3. C DISTINCT cannot be sorted
  4. D ORDER BY must come first

Answer: A — The ORDER BY columns must appear in the SELECT list

After DISTINCT collapses rows, a column that was not selected no longer has a single well-defined value to sort by, so most databases reject it.

DISTINCTORDER BY

Practice “Sorting & Limiting” as a quiz