SELECT with PostgreSQL-specific features - Step-by-Step Execution

Concept Flow - SELECT with PostgreSQL-specific features

Start: Write SELECT query

↓

Parse query syntax

↓

Check for PostgreSQL-specific features

↓

Handle DISTINCT ON

↓

Return first row per group

↓

Handle RETURNING clause

↓

Return affected rows after INSERT/UPDATE/DELETE

↓

Handle FILTER clause

↓

Apply condition to aggregate functions

↓

Execute query

↓

Return result set

The flow shows how PostgreSQL parses a SELECT query, checks for special features like DISTINCT ON, RETURNING, and FILTER, processes them, then executes and returns results.

Execution Sample

PostgreSQL

SELECT DISTINCT ON (category) id, category, price
FROM products
ORDER BY category, price DESC;

This query selects one row per category from products, choosing the row with the highest price in each category.

Execution Table

Step	Action	Evaluation	Result
1	Parse SELECT statement	Valid syntax	Proceed to next step
2	Identify DISTINCT ON clause	DISTINCT ON (category)	Prepare to select first row per category
3	Order rows by category ASC, price DESC	Ordering applied	Rows sorted accordingly
4	Scan rows in order	First row of each category encountered	Select that row
5	Skip other rows with same category	Duplicate categories ignored	Only first per category kept
6	Return selected rows	Result set ready	Rows with unique categories and highest price returned
7	End of query execution	All rows processed	Execution complete

💡 All rows processed and first row per category returned due to DISTINCT ON

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
Rows	Unordered full table	Sorted by category ASC, price DESC	First row per category identified	Duplicates skipped	Result set with unique categories

Key Moments - 3 Insights

Why does DISTINCT ON select only the first row per category?

DISTINCT ON works with ORDER BY to pick the first row it encounters for each distinct category. See execution_table rows 3-5 where ordering happens first, then the first row per category is selected.

What happens if ORDER BY does not include the DISTINCT ON columns?

PostgreSQL requires ORDER BY to start with the DISTINCT ON columns to define which row is first per group. Without this, the query will error or behave unpredictably. This is implied in step 3 of the execution_table.

Is DISTINCT ON standard SQL?

No, DISTINCT ON is a PostgreSQL-specific feature. Other databases do not support it, so this query is PostgreSQL-specific as shown in the concept_flow.

Visual Quiz - 3 Questions

Look at the execution_table, at which step are duplicate categories skipped?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Check the 'Action' column in execution_table row 5 about skipping duplicates

According to variable_tracker, what is the state of rows after Step 3?

A Unordered full table

B Sorted by category ASC, price DESC

C First row per category identified

D Duplicates skipped

💡 Hint

Look at the 'After Step 3' column for 'Rows' in variable_tracker

If ORDER BY did not include category, what would happen?

A PostgreSQL would raise an error

B DISTINCT ON would select random rows per category

C Query would run normally

D All rows would be returned

💡 Hint

Refer to key_moments about ORDER BY requirements for DISTINCT ON

Concept Snapshot

SELECT with PostgreSQL-specific features:
- DISTINCT ON (expression) picks first row per group
- ORDER BY must start with DISTINCT ON columns
- RETURNING returns affected rows after data changes
- FILTER applies conditions inside aggregates
Use these to write powerful PostgreSQL queries.

Full Transcript

This visual execution trace shows how PostgreSQL processes a SELECT query with the DISTINCT ON feature. The query orders rows by category and price, then selects the first row per category, effectively returning the highest priced item in each category. The flow starts with parsing, then identifying PostgreSQL-specific clauses, applying ordering, scanning rows, skipping duplicates, and returning the final result set. Variables like the row order and selection state change step-by-step. Key moments clarify why ORDER BY must include DISTINCT ON columns and that DISTINCT ON is PostgreSQL-specific. The quiz tests understanding of when duplicates are skipped, the state of rows after sorting, and the importance of ORDER BY for DISTINCT ON.

Practice

1. What does the LIMIT clause do in a PostgreSQL SELECT query?

easy

A. It combines rows from two tables.

B. It restricts the number of rows returned by the query.

C. It removes duplicate rows from the result.

D. It sorts the rows in ascending order.

Solution

1. Step 1: Understand the purpose of LIMIT

   The LIMIT clause is used to specify the maximum number of rows to return from a query result.

2. Step 2: Compare with other options

   Sorting is done by ORDER BY, removing duplicates by DISTINCT, and combining tables by JOIN, so these are not correct.

3. Final Answer:

   It restricts the number of rows returned by the query. -> Option B

4. Quick Check:

   LIMIT = restrict rows [OK]

Hint: LIMIT caps rows returned, like a page size [OK]

Common Mistakes:

• Confusing LIMIT with ORDER BY
• Thinking LIMIT removes duplicates
• Mixing LIMIT with JOIN functionality

2. Which of the following is the correct syntax to select distinct city names from a table called customers?

easy

A. SELECT city FROM DISTINCT customers;

B. SELECT city DISTINCT FROM customers;

C. SELECT DISTINCT city FROM customers;

D. SELECT city FROM customers DISTINCT;

Solution

1. Step 1: Recall correct DISTINCT syntax

   In PostgreSQL, DISTINCT comes immediately after SELECT and before the column list.

2. Step 2: Validate each option

   SELECT DISTINCT city FROM customers; correctly places DISTINCT after SELECT. Options A, B, and D misuse DISTINCT placement causing syntax errors.

3. Final Answer:

   SELECT DISTINCT city FROM customers; -> Option C

4. Quick Check:

   SELECT DISTINCT = correct syntax [OK]

Hint: DISTINCT always follows SELECT keyword [OK]

Common Mistakes:

• Placing DISTINCT after column names
• Using DISTINCT after FROM clause
• Misordering keywords causing syntax errors

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

SELECT name FROM products ORDER BY price DESC LIMIT 2;

medium

A. The two products with the lowest prices.

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

C. All products sorted by price descending.

D. The two products with the highest prices.

Solution

1. Step 1: Understand ORDER BY with DESC

   ORDER BY price DESC sorts products from highest to lowest price.

2. Step 2: Apply LIMIT 2

   LIMIT 2 restricts the output to only the first two rows after sorting, so the two most expensive products.

3. Final Answer:

   The two products with the highest prices. -> Option D

4. Quick Check:

   ORDER BY DESC + LIMIT = top rows [OK]

Hint: ORDER BY DESC + LIMIT picks top items [OK]

Common Mistakes:

• Thinking LIMIT returns lowest prices
• Believing LIMIT causes syntax error with ORDER BY
• Ignoring DESC means descending order

4. Identify the error in this PostgreSQL query:

SELECT DISTINCT name, price FROM products LIMIT;

medium

A. LIMIT requires a number to specify how many rows to return.

B. DISTINCT cannot be used with multiple columns.

C. SELECT must include WHERE clause when using LIMIT.

D. The table name is missing.

Solution

1. Step 1: Check LIMIT usage

   LIMIT must be followed by a number indicating how many rows to return; here it is missing.

2. Step 2: Validate other parts

   DISTINCT can be used with multiple columns, WHERE is optional, and table name is present as products.

3. Final Answer:

   LIMIT requires a number to specify how many rows to return. -> Option A

4. Quick Check:

   LIMIT needs a number [OK]

Hint: Always put a number after LIMIT [OK]

Common Mistakes:

• Omitting number after LIMIT
• Thinking DISTINCT can't use multiple columns
• Assuming WHERE is mandatory with LIMIT

5. You want to get the third and fourth highest salaries from the employees table with column salary. Which query correctly achieves this in PostgreSQL?

hard

A. SELECT salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2;

B. SELECT salary FROM employees ORDER BY salary ASC LIMIT 2 OFFSET 3;

C. SELECT DISTINCT salary FROM employees LIMIT 2 OFFSET 3 ORDER BY salary DESC;

D. SELECT salary FROM employees LIMIT 2 OFFSET 2 ORDER BY salary DESC;

Solution

1. Step 1: Understand OFFSET and LIMIT

   OFFSET 2 skips the first two highest salaries, LIMIT 2 returns the next two rows.

2. Step 2: Check ORDER BY direction

   ORDER BY salary DESC sorts salaries from highest to lowest, so OFFSET 2 skips top two highest.

3. Step 3: Validate options

   SELECT salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2; correctly orders DESC, uses LIMIT 2 and OFFSET 2. SELECT salary FROM employees ORDER BY salary ASC LIMIT 2 OFFSET 3; orders ASC (wrong direction), C misplaces ORDER BY, D places ORDER BY after LIMIT (syntax error).

4. Final Answer:

   SELECT salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2; -> Option A

5. Quick Check:

   OFFSET skips rows, LIMIT picks next [OK]

Hint: Use OFFSET to skip, LIMIT to pick rows [OK]

Common Mistakes:

• Using ASC instead of DESC for highest values
• Placing ORDER BY after LIMIT
• Confusing OFFSET number with row count