LIMIT/TOP - SQL

1. Consider this query: SELECT event_id FROM audit_log WHERE severity = 'HIGH' LIMIT 4; Which outcome is guaranteed?
   Answer: Exactly four qualifying rows are returned, but the selected rows are not predictable without ORDER BY. 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.
2. What result is produced by this PostgreSQL-compatible query? SELECT DISTINCT category FROM products ORDER BY category DESC LIMIT 2;
   Answer: Trial, Standard, because duplicates are removed before the distinct categories are ordered and limited. 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.
3. 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;
   Answer: 80, 90, 95, because the inner query selects the top three and the outer query reorders them. 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.
4. 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?
   Answer: It returns at most three joined rows, which may all belong to customer 101. 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.
5. If the second request uses LIMIT 3 OFFSET 3, which result and pagination issue should the application expect?
   Answer: IDs 7, 6, 5; ID 7 repeats because the inserted row shifted the offset positions. 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.
6. How many rows does this query return? SELECT TOP (15) PERCENT order_id FROM orders WHERE status = 'READY' ORDER BY priority DESC;
   Answer: Three rows, because the fractional percentage result is rounded up to a whole row. 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 \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 $\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.
7. Which SQL Server query is the appropriate rewrite?
   Answer: SELECT TOP (5) ticket_id, created_at FROM tickets ORDER BY created_at DESC; 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.
8. What does the following PostgreSQL-compatible clause return from that ordered result? LIMIT 4 OFFSET 5
   Answer: Customer IDs 63, 12, 74, 29, because the first five rows are skipped. 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.
9. 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;
   Answer: West and North, because qualifying groups are ordered ascending before limiting. 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.
10. How many rows are returned by this query? SELECT TOP (2) WITH TIES employee_id, score FROM results ORDER BY score DESC;
    Answer: Four rows, because every row tied at the second row's score is included. 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.

Start this quiz