Why result control matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query with LIMIT?

Consider a table employees with columns id and name. What will be the output of this query?

SELECT id, name FROM employees ORDER BY id LIMIT 2;

PostgreSQL

SELECT id, name FROM employees ORDER BY id LIMIT 2;

A [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]

B [{"id": 3, "name": "Charlie"}, {"id": 4, "name": "Diana"}]

C [{"id": 2, "name": "Bob"}, {"id": 1, "name": "Alice"}]

D []

💡 Hint

LIMIT controls how many rows are returned after sorting.

The query orders employees by id ascending and returns only the first two rows, which are Alice (id 1) and Bob (id 2).

❓ query_result

intermediate

What happens if ORDER BY is missing with LIMIT?

Given the same employees table, what is the output of this query?

SELECT id, name FROM employees LIMIT 2;

Assume the table has rows with ids 1 to 4.

PostgreSQL

SELECT id, name FROM employees LIMIT 2;

A [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]

B [{"id": 3, "name": "Charlie"}, {"id": 4, "name": "Diana"}]

C Empty result

D Any two rows, order not guaranteed

💡 Hint

Without ORDER BY, the database can return any rows first.

Without ORDER BY, the database does not guarantee which rows come first, so LIMIT 2 returns any two rows.

📝 Syntax

advanced

Which query correctly limits results after filtering?

You want to get the first 3 employees with id greater than 1, ordered by name. Which query is correct?

A SELECT id, name FROM employees LIMIT 3 WHERE id > 1 ORDER BY name;

B SELECT id, name FROM employees ORDER BY name LIMIT 3 WHERE id > 1;

C SELECT id, name FROM employees WHERE id > 1 ORDER BY name LIMIT 3;

D SELECT id, name FROM employees WHERE id > 1 LIMIT 3 ORDER BY name;

💡 Hint

Remember the order of clauses in SQL: WHERE, ORDER BY, then LIMIT.

The correct order is WHERE to filter, then ORDER BY to sort, then LIMIT to restrict rows.

❓ optimization

advanced

Why is controlling result order important for pagination?

You want to show page 2 of employees with 2 rows per page, ordered by id. Which query correctly gets the right rows?

A SELECT id, name FROM employees ORDER BY id LIMIT 2 OFFSET 2;

B SELECT id, name FROM employees WHERE id > 2 LIMIT 2 ORDER BY id;

C SELECT id, name FROM employees OFFSET 2 LIMIT 2 ORDER BY id;

D SELECT id, name FROM employees LIMIT 2 OFFSET 2 ORDER BY id;

💡 Hint

OFFSET skips rows after ordering, LIMIT restricts how many to return.

ORDER BY must come before LIMIT and OFFSET to ensure correct rows are selected for pagination.

🧠 Conceptual

expert

What is the risk of missing ORDER BY when limiting results?

Why is it important to use ORDER BY when using LIMIT in queries that fetch data for user display?

A Without ORDER BY, the query will always return the first rows inserted into the table.

B Without ORDER BY, the rows returned can be random and inconsistent between queries.

C Without ORDER BY, the database will return an error when LIMIT is used.

D Without ORDER BY, the LIMIT clause is ignored and all rows are returned.

💡 Hint

Think about user experience when data order changes unexpectedly.

ORDER BY ensures consistent and predictable row order. Without it, LIMIT returns arbitrary rows, causing confusion.

Practice

1. Why is result control important when querying a database?

easy

A. It deletes unnecessary data from the database.

B. It makes the database run slower.

C. It helps you get clear and useful data by filtering and sorting.

D. It automatically fixes errors in your data.

Solution

1. Step 1: Understand what result control does

   Result control means filtering, sorting, and limiting data to get clear results.

2. Step 2: Identify the benefit of result control

   It helps avoid confusion and saves time by showing only useful data.

3. Final Answer:

   It helps you get clear and useful data by filtering and sorting. -> Option C

4. Quick Check:

   Result control = clear, useful data [OK]

Hint: Result control shapes data output for clarity and usefulness [OK]

Common Mistakes:

• Thinking result control deletes data
• Believing it slows down queries
• Assuming it fixes data errors automatically

2. Which of the following is the correct SQL syntax to limit query results to 5 rows in PostgreSQL?

easy

A. SELECT * FROM table WHERE ROWS = 5;

B. SELECT * FROM table TOP 5;

C. SELECT * FROM table FETCH FIRST 5;

D. SELECT * FROM table LIMIT 5;

Solution

1. Step 1: Recall PostgreSQL syntax for limiting rows

   PostgreSQL uses LIMIT to restrict the number of rows returned.

2. Step 2: Compare options with correct syntax

   SELECT * FROM table LIMIT 5; uses LIMIT 5, which is correct. SELECT * FROM table TOP 5; uses TOP, which is SQL Server syntax. SELECT * FROM table FETCH FIRST 5; is invalid syntax (missing ROWS ONLY). SELECT * FROM table WHERE ROWS = 5; is invalid syntax.

3. Final Answer:

   SELECT * FROM table LIMIT 5; -> Option D

4. Quick Check:

   LIMIT 5 limits rows in PostgreSQL [OK]

Hint: Use LIMIT to restrict rows in PostgreSQL queries [OK]

Common Mistakes:

• Using TOP instead of LIMIT
• Trying FETCH FIRST without correct syntax
• Using WHERE to limit rows

3. Given the table employees with columns id, name, and salary, what will this query return?

SELECT name FROM employees ORDER BY salary DESC LIMIT 3;

medium

A. The names of the 3 employees with the highest salaries.

B. The names of the 3 employees with the lowest salaries.

C. The names of all employees sorted by salary ascending.

D. An error because LIMIT cannot be used with ORDER BY.

Solution

1. Step 1: Understand ORDER BY salary DESC

   This sorts employees by salary from highest to lowest.

2. Step 2: Apply LIMIT 3

   Limits the output to the top 3 rows after sorting.

3. Final Answer:

   The names of the 3 employees with the highest salaries. -> Option A

4. Quick Check:

   ORDER BY DESC + LIMIT 3 = top 3 highest [OK]

Hint: ORDER BY DESC then LIMIT gets top N results [OK]

Common Mistakes:

• Confusing DESC with ASC order
• Thinking LIMIT applies before sorting
• Believing LIMIT causes syntax error with ORDER BY

4. You wrote this query but it returns all rows instead of just 10:

SELECT * FROM sales WHERE amount > 100 LIMIT 10 OFFSET 5;

What is the most likely reason?

medium

A. The query is correct; it returns only 10 rows after skipping 5.

B. The WHERE condition is incorrect and matches all rows.

C. LIMIT and OFFSET must be in uppercase to work.

D. The OFFSET clause is not supported in PostgreSQL.

Solution

1. Step 1: Check syntax of LIMIT and OFFSET

   PostgreSQL supports LIMIT and OFFSET in any case and syntax is correct.

2. Step 2: Understand WHERE clause effect

   WHERE amount > 100 filters rows; if many match, LIMIT 10 OFFSET 5 returns 10 rows after skipping 5.

3. Final Answer:

   The query is correct; it returns only 10 rows after skipping 5. -> Option A

4. Quick Check:

   LIMIT + OFFSET works as expected in PostgreSQL [OK]

Hint: LIMIT and OFFSET control rows returned and skipped [OK]

Common Mistakes:

• Thinking OFFSET is unsupported
• Believing case sensitivity matters for keywords
• Assuming WHERE is ignored

5. You want to get the top 3 highest paid employees from each department in a table employees(department, name, salary). Which approach best controls the result to achieve this?

hard

A. Use ORDER BY salary DESC LIMIT 3 without partitioning.

B. Use a window function with ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) and filter rows where row number ≤ 3.

C. Use GROUP BY department and MAX(salary) only.

D. Use LIMIT 3 and GROUP BY name.

Solution

1. Step 1: Understand the need for top N per group

   We want top 3 salaries per department, not overall top 3.

2. Step 2: Identify correct SQL technique

   Window functions with ROW_NUMBER() partitioned by department rank salaries per group.

3. Step 3: Filter rows with row number ≤ 3

   This returns top 3 employees per department correctly.

4. Final Answer:

   Use a window function with ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) and filter rows where row number ≤ 3. -> Option B

5. Quick Check:

   Window functions + filtering = top N per group [OK]

Hint: Use ROW_NUMBER() with PARTITION BY for top N per group [OK]

Common Mistakes:

• Using LIMIT without partitioning
• Using GROUP BY without ranking
• Confusing GROUP BY columns