SQL Quiz: Limit Top
10 questions · exam conditions
0:00
Limit TopQuestion 1 of 10

In SQL Server, the WHERE clause of a query leaves 18 qualifying rows. Assume the ORDER BY values are unique, so WITH TIES is not involved.

How many rows does this query return?

SELECT TOP (15) PERCENT order_id FROM orders WHERE status = 'READY' ORDER BY priority DESC;

Two rows, because the fractional result is truncated to the next lower whole row.
Three rows, because the fractional percentage result is rounded up to a whole row.
Fifteen rows, because the value in TOP remains a row count despite PERCENT.
Eighteen rows, because percentages below one full group do not restrict the result.
← Back to quizzes

SQL Quiz

SQL Quiz: Limit Top

Practice Limit Top in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Limit Top, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

In SQL Server, the WHERE clause of a query leaves 18 qualifying rows. Assume the ORDER BY values are unique, so WITH TIES is not involved.

How many rows does this query return?

SELECT TOP (15) PERCENT order_id FROM orders WHERE status = 'READY' ORDER BY priority DESC;

  1. Two rows, because the fractional result is truncated to the next lower whole row.
  2. Three rows, because the fractional percentage result is rounded up to a whole row. (correct answer)
  3. Fifteen rows, because the value in TOP remains a row count despite PERCENT.
  4. Eighteen rows, because percentages below one full group do not restrict the result.
Explanation: When you use TOP with PERCENT in SQL Server, the engine calculates a percentage of the qualifying row count and then rounds up to the nearest whole number — this ceiling behavior is the core concept being tested here. With 18 rows passing the WHERE clause, the calculation is: 18×0.15=2.7 rows18 \times 0.15 = 2.7 \text{ rows} Because SQL Server always rounds fractional results up (ceiling, not truncation), 2.7 becomes 3 rows returned. That makes B the correct answer. A describes truncation — rounding 2.7 down to 2. This is the most tempting trap because truncation feels intuitive for whole-row counts, but SQL Server explicitly uses ceiling rounding for TOP PERCENT. Memorizing this distinction is essential. C misreads the syntax entirely, treating 15 as a literal row count rather than a percentage. The keyword PERCENT after the value changes the behavior fundamentally — TOP (15) and TOP (15) PERCENT are very different queries. D invents a rule that doesn't exist. There is no concept of a "full group threshold" in TOP PERCENT logic. The percentage always restricts the result; it never returns all qualifying rows simply because the percentage is small. A useful study tip: whenever you see TOP (n) PERCENT, always apply the formula ⌈qualifying rows×(n/100)⌉\lceil \text{qualifying rows} \times (n / 100) \rceil — the ceiling function. SQL Server never truncates a fractional row count from PERCENT; it always rounds up, ensuring at least one row is returned even for very small percentages.

Question 2

The products table contains category values Basic, Premium, Basic, Standard, Premium, Trial, Standard.

What result is produced by this PostgreSQL-compatible query?

SELECT DISTINCT category FROM products ORDER BY category DESC LIMIT 2;

  1. Trial, Standard, because duplicates are removed before the distinct categories are ordered and limited. (correct answer)
  2. Trial, Premium, because LIMIT 2 selects the first two physical rows before DISTINCT and ORDER BY are applied.
  3. Standard, Premium, because duplicate categories consume result positions before DISTINCT is applied.
  4. Basic, Premium, because LIMIT uses the table's original row order before sorting.
Explanation: When you see a query combining DISTINCT, ORDER BY, and LIMIT, the key is understanding SQL's logical processing order: DISTINCT is applied first to eliminate duplicates, then ORDER BY sorts the deduplicated results, and finally LIMIT trims the output. These clauses don't compete — they form a pipeline. Starting with the raw category values — Basic, Premium, Basic, Standard, Premium, Trial, Standard — DISTINCT collapses them into four unique categories: Basic, Premium, Standard, Trial. Next, ORDER BY category DESC sorts them in reverse alphabetical order: Trial, Standard, Premium, Basic. Finally, LIMIT 2 takes only the first two rows from that sorted list, giving you Trial, Standard. That makes A correct. B is wrong because LIMIT never acts on raw physical rows before other clauses are processed — it always comes last in the logical pipeline. C reflects a misunderstanding that duplicate rows somehow "consume" result positions before DISTINCT removes them; once deduplication runs, duplicates are simply gone and don't affect how many rows LIMIT sees. D incorrectly suggests LIMIT respects the table's original insertion order rather than the sorted order — ORDER BY always governs the sequence that LIMIT pulls from. A useful mental model: think of DISTINCT → ORDER BY → LIMIT as a funnel. Data is deduplicated, then arranged, then cut. Any question trying to make you believe LIMIT or ORDER BY runs before DISTINCT is exploiting the gap between physical storage order and logical query processing — a very common trap in SQL exams.

Question 3

After filtering, a query produces customers ordered by lifetime_value DESC, customer_id ASC. The ordered customer IDs are 41, 18, 32, 27, 55, 63, 12, 74, 29, 86, 90, 95.

What does the following PostgreSQL-compatible clause return from that ordered result?

LIMIT 4 OFFSET 5

  1. Customer IDs 55, 63, 12, 74, because the fifth row begins the returned range.
  2. Customer IDs 63, 12, 74, 29, because the first five rows are skipped. (correct answer)
  3. Customer IDs 63, 12, 74, 29, 86, because the offset determines the result size.
  4. Customer IDs 12, 74, 29, 86, because six rows are skipped before applying the limit.
Explanation: When you see LIMIT and OFFSET together in SQL, think of it as a two-step process: first skip rows, then take rows. OFFSET 5 discards the first 5 rows entirely, and LIMIT 4 then returns the next 4 rows from whatever remains. Starting with the ordered sequence 41, 18, 32, 27, 55, 63, 12, 74, 29, 86, 90, 95, skipping the first 5 rows removes positions 1–5: 41, 18, 32, 27, 55. The remaining sequence begins at position 6 with 63. Applying LIMIT 4 then takes the next four rows: 63, 12, 74, 29. That confirms B is correct. Choice A claims the fifth row begins the returned range, meaning only 4 rows are skipped. That confuses OFFSET 5 with OFFSET 4 — remember, offset is zero-based in effect: skipping 5 rows lands you at the 6th row, not the 5th. Choice C returns five rows (63, 12, 74, 29, 86), misreading OFFSET as the result size rather than the number of rows to skip. The offset controls what you skip; LIMIT controls how many you return. Choice D claims six rows are skipped, which would place the start at position 7 (12). This adds an extra skipped row that doesn't exist — OFFSET 5 skips exactly five rows, full stop. A reliable trick: mentally index your rows starting at 1, count off OFFSET rows to discard, then count LIMIT rows forward. That two-step mental model will save you on any pagination question.

Question 4

The exam_scores table contains scores 95, 90, 80, 70, 60, with no duplicate scores.

In a PostgreSQL-compatible database, in what order are scores returned by this query?

SELECT score FROM (SELECT score FROM exam_scores ORDER BY score DESC LIMIT 3) AS highest_scores ORDER BY score ASC;

  1. 95, 90, 80, because the inner descending order remains final after the outer query.
  2. 60, 70, 80, because the outer ascending order is applied before the inner limit.
  3. 80, 90, 95, because the inner query selects the top three and the outer query reorders them. (correct answer)
  4. 70, 80, 90, because each ordering removes one endpoint before the limit is applied.
Explanation: When you see a nested SELECT with ORDER BY clauses at multiple levels, the key is understanding that SQL evaluates subqueries from the inside out — each layer produces a result set that the outer layer then works with independently. Here, the inner query SELECT score FROM exam_scores ORDER BY score DESC LIMIT 3 first sorts all five scores descending (95, 90, 80, 70, 60) and then takes the top three, producing the set {95, 90, 80}. The outer query then receives exactly those three rows and applies its own ORDER BY score ASC, reordering them into 80, 90, 95. That's why C is correct — the inner query selects, the outer query reorders. A assumes the inner descending order "sticks" and overrides the outer clause. It doesn't — any ORDER BY in an outer query replaces whatever ordering the subquery produced. The outer ASC is the final authority on ordering. B reflects a backwards mental model where the outer query somehow runs first. SQL never applies an outer filter before an inner one completes. The LIMIT in the subquery has already locked in the three highest scores before the outer query even begins. D invents a fictional rule where each ORDER BY "removes an endpoint," giving 70, 80, 90. No such behavior exists in SQL — ORDER BY resequences rows, it doesn't filter them. A useful pattern to remember: treat every subquery as a black box that fully executes first. Once you know what rows it returns, ask yourself what the outer query does to just those rows.

Question 5

After the WHERE status = 'SHIPPED' filter, regional sales totals are: East 150, North 120, South 90, and West 110.

Which regions are returned by this PostgreSQL-compatible query?

SELECT region, SUM(amount) AS total FROM orders WHERE status = 'SHIPPED' GROUP BY region HAVING SUM(amount) >= 100 ORDER BY total ASC LIMIT 2;

  1. South and West, because limiting occurs before the HAVING condition removes groups.
  2. East and North, because the two largest qualifying totals are retained by default.
  3. West and North, because qualifying groups are ordered ascending before limiting. (correct answer)
  4. North and East, because grouping order determines which two groups reach ORDER BY.
Explanation: When reading a query like this, you need to mentally trace the SQL execution order: WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. The LIMIT clause is always applied last, after filtering and sorting are complete. Here's the step-by-step logic that makes C correct: After the WHERE status = 'SHIPPED' filter, you have four regional totals — East (150), North (120), South (90), West (110). The HAVING SUM(amount) >= 100 clause then eliminates South (90), leaving East (150), North (120), and West (110). Next, ORDER BY total ASC sorts these three qualifying groups from smallest to largest: West (110), North (120), East (150). Finally, LIMIT 2 takes the first two rows from that sorted result — West and North — making C the correct answer. A is wrong because it reverses the execution order — HAVING always filters groups before LIMIT truncates the result set. South would have been removed before any limiting occurs. B is wrong because it assumes LIMIT 2 keeps the largest values, but with ORDER BY total ASC (ascending), the two smallest qualifying totals are returned first, not the largest. D is wrong because it claims grouping order influences which rows reach ORDER BY. In SQL, GROUP BY does not guarantee any output ordering — only ORDER BY determines the final sequence. Your study tip: memorize the SQL clause execution order as WGHOL — Where, Group by, Having, Order by, Limit. Any question mixing these clauses is testing whether you apply them in the right sequence.

Question 6

An application paginates posts using ORDER BY post_id DESC LIMIT 3 OFFSET n. Its first request returns post IDs 9, 8, 7. Before the second request, a new post with ID 10 is inserted. No other data changes.

If the second request uses LIMIT 3 OFFSET 3, which result and pagination issue should the application expect?

  1. IDs 6, 5, 4; the offset is anchored to the original first page despite the insertion.
  2. IDs 7, 6, 5; ID 7 repeats because the inserted row shifted the offset positions. (correct answer)
  3. IDs 8, 7, 6; both prior rows repeat because the limit is recalculated after insertion.
  4. IDs 6, 5, 4; the new row is ignored until all existing pages have been requested.
Explanation: When working with offset-based pagination in SQL, you need to think dynamically — the database doesn't remember your previous query. Each request is evaluated fresh against whatever data currently exists in the table. Here's how to trace through the problem. Before the first request, the ordered sequence (by post_id DESC) is 9, 8, 7, 6, 5, 4.... LIMIT 3 OFFSET 0 returns 9, 8, 7. Then post ID 10 is inserted. Now the full ordered sequence is 10, 9, 8, 7, 6, 5, 4.... When the second request runs LIMIT 3 OFFSET 3, the database skips the first 3 rows (10, 9, 8) and returns the next 3: 7, 6, 5. ID 7 appeared on the first page, and now it appears again — a classic phantom read / row duplication problem caused by offset shifting after an insertion. A is wrong because the offset is not anchored to the original dataset. SQL has no memory of prior queries, so claiming the result is stable at 6, 5, 4 misrepresents how OFFSET works. C is wrong because 8, 7, 6 would only result if two rows shifted, but only one new row was inserted, causing only one position of overlap. D is wrong because SQL never "ignores" new rows pending some application-level state — the database simply executes the query against live data. As a study tip, remember that OFFSET-based pagination is vulnerable to both row duplication (on inserts) and row skipping (on deletes). A more robust alternative is keyset pagination, using a WHERE post_id < last_seen_id clause instead.

Question 7

In SQL Server, the six highest distinct employee scores are 98, 94, 94, 94, 91, 88. Each listed score belongs to one employee.

How many rows are returned by this query?

SELECT TOP (2) WITH TIES employee_id, score FROM results ORDER BY score DESC;

  1. Two rows, because TOP (2) always imposes an absolute two-row maximum.
  2. Three rows, because only rows after the second physical row can qualify as ties.
  3. Four rows, because every row tied at the second row's score is included. (correct answer)
  4. Six rows, because WITH TIES includes every row having any duplicate score.
Explanation: When you see TOP (n) WITH TIES in SQL Server, the key is understanding that WITH TIES doesn't simply add duplicates of every value — it expands the result set to include all rows that match the score of the last row returned by TOP (n). Here's how it works with these scores: TOP (2) first identifies the two highest-scoring rows, which have scores of 98 and 94. The last (second) row's score is 94. Because WITH TIES is specified, SQL Server then pulls in every additional row that also has a score of 94. Since three employees scored 94, all three are included alongside the one employee with 98 — giving you four rows total, making C correct. A is wrong because it treats TOP (2) as a hard cap. WITH TIES explicitly overrides that cap when matching scores exist at the boundary — the whole point of the clause is to break past the strict row limit. B reflects a common misreading: that only rows after the second physical row can qualify as ties. In reality, WITH TIES looks at the score of the second row and includes any row sharing that score, regardless of physical position. D overstates WITH TIES dramatically. It does not include every row with any duplicate score in the entire table — it only expands based on the score at the TOP (n) boundary. Scores below 94 are never considered. A useful memory anchor: think of WITH TIES as "include everyone who tied for the last seat." The cutoff score is what matters, not the total count of duplicates in the table.

Question 8

A PostgreSQL query returns the five most recently created tickets using ORDER BY created_at DESC LIMIT 5. It must be rewritten for SQL Server while preserving the same intended ordering and maximum row count.

Which SQL Server query is the appropriate rewrite?

  1. SELECT ticket_id, created_at TOP (5) FROM tickets ORDER BY created_at DESC;
  2. SELECT ticket_id, created_at FROM tickets ORDER BY TOP (5) created_at DESC;
  3. SELECT ticket_id, created_at FROM tickets LIMIT 5 ORDER BY created_at DESC;
  4. SELECT TOP (5) ticket_id, created_at FROM tickets ORDER BY created_at DESC; (correct answer)
Explanation: When migrating SQL queries between database systems, one of the most common compatibility issues involves row-limiting syntax — each database has its own way of restricting how many rows a query returns. PostgreSQL uses LIMIT n appended at the end of a query, while SQL Server uses TOP (n) placed immediately after SELECT. Recognizing this difference is the core skill being tested here. The correct rewrite is D: SELECT TOP (5) ticket_id, created_at FROM tickets ORDER BY created_at DESC. This follows SQL Server's required syntax exactly — TOP (n) must appear right after the SELECT keyword, before any column names. The ORDER BY created_at DESC then ensures the five rows returned are the five most recent tickets, preserving the original intent. A is wrong because it places TOP (5) between the column list and FROM, which is syntactically invalid in SQL Server — TOP cannot appear there. B is wrong because it inserts TOP (5) inside the ORDER BY clause, which is also invalid syntax. TOP belongs in the SELECT clause, not in the ordering clause. C is wrong because it uses LIMIT 5, which is PostgreSQL (and MySQL/SQLite) syntax. SQL Server does not recognize LIMIT as a valid keyword, so this query would throw an error. A useful memory aid: in SQL Server, think of TOP as a modifier to SELECT — it tells SELECT how many rows to pick. Always write it as SELECT TOP (n) columns..., and you'll never misplace it.

Question 9

A PostgreSQL-compatible database contains at least 20 rows in audit_log for which severity = 'HIGH'. No rows are added, removed, or updated while the query runs.

Consider this query:

SELECT event_id FROM audit_log WHERE severity = 'HIGH' LIMIT 4;

Which outcome is guaranteed?

  1. Exactly four qualifying rows are returned, but the selected rows are not predictable without ORDER BY. (correct answer)
  2. The four qualifying rows having the smallest event_id values are returned by default.
  3. The same four qualifying rows are returned whenever the table contents remain unchanged.
  4. All qualifying rows are sorted by physical storage position before four rows are returned.
Explanation: When you see a question about LIMIT without ORDER BY, your focus should immediately shift to what SQL actually guarantees versus what might happen to be true in practice. In SQL (and specifically PostgreSQL), LIMIT restricts how many rows are returned, but without an ORDER BY clause, the database engine is free to retrieve rows in any order it finds convenient — typically whatever is fastest. This might mean following physical storage order, using an index scan, or pulling from a cache. Because no ordering is specified, exactly four qualifying rows will be returned, but which four is entirely up to the query planner. That makes A correct: you are guaranteed a count of four, but the selection is unpredictable. B is wrong because LIMIT does not imply any sorting by event_id. Only an explicit ORDER BY event_id would guarantee the smallest values are returned first. B describes what you'd get with ORDER BY event_id ASC LIMIT 4 — a common trap that conflates row identity with row order. C is wrong because even with unchanging table contents, the query planner may choose different access paths across executions — for example, after statistics updates or plan cache changes — meaning the same four rows are not reliably returned. Stability isn't guaranteed without ORDER BY. D is wrong because SQL does not sort all qualifying rows by physical storage position before applying LIMIT. The engine may stop scanning as soon as it finds enough rows, without ever evaluating the rest. Study tip: Anytime you see LIMIT without ORDER BY, remember: count is guaranteed, identity is not. If you need specific rows, always pair LIMIT with ORDER BY.

Question 10

A customer can have many orders. Customer 101 has the five newest orders in the database, followed by orders belonging to other customers.

Consider this PostgreSQL-compatible query:

SELECT c.customer_id, o.order_id FROM customers AS c JOIN orders AS o ON o.customer_id = c.customer_id ORDER BY o.created_at DESC LIMIT 3;

Which statement correctly describes the restriction imposed by LIMIT 3?

  1. It returns orders for exactly three customers, selecting one newest order from each customer.
  2. It returns every order for the three customers whose orders appear earliest after sorting.
  3. It first limits each table to three rows and then performs the join between them.
  4. It returns at most three joined rows, which may all belong to customer 101. (correct answer)
Explanation: When you see LIMIT in a SQL query, remember that it applies to the final result set — the rows produced after all joins, filters, and sorting are complete. It is not a per-table restriction, and it knows nothing about how many customers or groups are represented. In this query, the database first joins customers and orders, then sorts all resulting rows by created_at DESC (newest first), and only then applies LIMIT 3. Since customer 101 owns the five newest orders in the database, the top three rows after sorting all belong to 101. So LIMIT 3 simply hands back those three rows — all from the same customer. That makes D correct: the clause returns at most three joined rows, which can all belong to a single customer. A is wrong because LIMIT has no concept of "one row per customer." Distributing results evenly across customers would require DISTINCT ON, ROW_NUMBER(), or similar window-function logic — none of which appear here. B is wrong for a related reason: LIMIT doesn't identify three customers and retrieve all their orders. It simply cuts the sorted result list off at three rows, period. C describes a fundamental misconception — LIMIT is never pushed into individual tables before a join (at least not in a way that changes query semantics). The join runs first; LIMIT trims afterward. A useful rule of thumb: treat LIMIT as a pair of scissors sitting at the very end of the query pipeline. It cuts rows, not groups, not customers, not tables — just rows from the final output.