Query result ordering (ascending, descending) in DynamoDB - Step-by-Step Execution

Concept Flow - Query result ordering (ascending, descending)

Start Query

↓

Specify Partition Key

↓

Set ScanIndexForward

↓

Ascending

↓

Fetch Items in Order

↓

Return Results

The query starts by specifying the partition key, then sets the ScanIndexForward flag to true for ascending or false for descending order, fetches items accordingly, and returns the results.

Execution Sample

DynamoDB

Query(
  TableName='Orders',
  KeyConditionExpression='CustomerId = :cid',
  ExpressionAttributeValues={':cid': {'S': 'C123'}},
  ScanIndexForward=False
)

This query fetches orders for customer 'C123' in descending order by sort key.

Execution Table

Step	Action	ScanIndexForward	Order of Items Fetched	Result
1	Start Query with CustomerId = 'C123'	N/A	N/A	Query initialized
2	Set ScanIndexForward = False	False	Descending	Will fetch items from highest to lowest sort key
3	Fetch items matching CustomerId	False	Descending	Items fetched in descending order
4	Return results	False	Descending	Results returned in descending order
5	End	False	Descending	Query complete

💡 All matching items fetched and returned in descending order because ScanIndexForward is False

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
ScanIndexForward	N/A	False	False	False
Order of Items	N/A	N/A	Descending	Descending

Key Moments - 2 Insights

Why does setting ScanIndexForward to False return items in descending order?

Because ScanIndexForward controls the order of the sort key in the query result. False means descending order, as shown in execution_table step 2 and 3.

What happens if ScanIndexForward is not set?

By default, ScanIndexForward is True, so items are returned in ascending order of the sort key, as implied by the concept flow.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the value of ScanIndexForward?

A True

B False

C Null

D Not set

💡 Hint

Check the 'ScanIndexForward' column in execution_table row for step 2

At which step are items fetched in descending order?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Order of Items Fetched' column in execution_table

If ScanIndexForward was set to True, how would the order of items change?

A Items would be fetched in ascending order

B Items would be fetched in descending order

C No items would be fetched

D Order would be random

💡 Hint

Refer to the key_moments explanation about default ScanIndexForward behavior

Concept Snapshot

DynamoDB Query ordering:
- Use ScanIndexForward parameter
- True = ascending order (default)
- False = descending order
- Applies to sort key ordering
- Must specify partition key in query

Full Transcript

This visual execution shows how DynamoDB query result ordering works. The query starts by specifying the partition key. Then the ScanIndexForward flag is set. If it is False, items are fetched in descending order by the sort key. If True or not set, items are fetched ascending. The execution table traces each step from query start to returning results. The variable tracker shows ScanIndexForward stays False and order is descending. Key moments clarify why ScanIndexForward controls order and what happens if not set. The quiz tests understanding of ScanIndexForward value and its effect on item order.

Practice

1. In DynamoDB, which parameter controls whether query results are returned in ascending or descending order based on the sort key?

easy

A. ProjectionExpression

B. ReturnConsumedCapacity

C. ScanIndexForward

D. ConsistentRead

Solution

1. Step 1: Understand query ordering in DynamoDB

   DynamoDB orders query results based on the sort key, and the order can be controlled.

2. Step 2: Identify the controlling parameter

   The parameter ScanIndexForward controls ascending (true) or descending (false) order.

3. Final Answer:

   ScanIndexForward -> Option C

4. Quick Check:

   Ordering parameter = ScanIndexForward [OK]

Hint: Remember: ScanIndexForward controls ascending/descending order [OK]

Common Mistakes:

• Confusing ScanIndexForward with ReturnConsumedCapacity
• Thinking ordering applies to partition key
• Assuming default order is descending

2. Which of the following is the correct syntax to query a DynamoDB table named Orders with results in descending order on the sort key OrderDate?

easy

A. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :id', ExpressionAttributeValues: { ':id': '123' }, ScanIndexForward: true })

B. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :id', ExpressionAttributeValues: { ':id': '123' }, ScanIndexForward: false })

C. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :id', ExpressionAttributeValues: { ':id': '123' }, Descending: true })

D. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :id', ExpressionAttributeValues: { ':id': '123' }, OrderBy: 'DESC' })

Solution

1. Step 1: Identify the parameter for descending order

   To get descending order, ScanIndexForward must be set to false.

2. Step 2: Check the syntax correctness

   client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :id', ExpressionAttributeValues: { ':id': '123' }, ScanIndexForward: false }) uses ScanIndexForward: false correctly; other options use invalid or wrong parameters.

3. Final Answer:

   Option B syntax with ScanIndexForward false -> Option B

4. Quick Check:

   Descending order = ScanIndexForward false [OK]

Hint: Use ScanIndexForward false for descending order [OK]

Common Mistakes:

• Using ScanIndexForward true for descending order
• Using non-existent parameters like Descending or OrderBy
• Confusing partition key with sort key ordering

3. Given a DynamoDB table with partition key UserId and sort key Timestamp, what will be the order of results returned by this query?

client.query({
  TableName: 'UserActivity',
  KeyConditionExpression: 'UserId = :uid',
  ExpressionAttributeValues: { ':uid': 'user123' },
  ScanIndexForward: false
})

medium

A. Results unordered

B. Results ordered by Timestamp ascending (oldest first)

C. Results ordered by UserId ascending

D. Results ordered by Timestamp descending (newest first)

Solution

1. Step 1: Understand ScanIndexForward effect

   Setting ScanIndexForward: false returns results in descending order of the sort key.

2. Step 2: Identify the sort key

   The sort key is Timestamp, so results are ordered newest to oldest.

3. Final Answer:

   Results ordered by Timestamp descending (newest first) -> Option D

4. Quick Check:

   ScanIndexForward false = descending order [OK]

Hint: ScanIndexForward false means newest items first [OK]

Common Mistakes:

• Assuming ScanIndexForward false orders by partition key
• Thinking default order is descending
• Confusing ascending and descending meanings

4. You wrote this DynamoDB query but the results are always in ascending order, even though you want descending order:

client.query({
  TableName: 'Sales',
  KeyConditionExpression: 'StoreId = :sid',
  ExpressionAttributeValues: { ':sid': 'store1' },
  ScanIndexForward: 'false'
})

What is the error?

medium

A. ScanIndexForward must be a boolean, not a string

B. KeyConditionExpression is incorrect

C. ExpressionAttributeValues is missing a value

D. TableName is invalid

Solution

1. Step 1: Check ScanIndexForward data type

   ScanIndexForward expects a boolean true or false, not a string.

2. Step 2: Identify impact of wrong type

   Passing 'false' as a string is truthy, so DynamoDB treats it as true (ascending order).

3. Final Answer:

   ScanIndexForward must be boolean false, not string 'false' -> Option A

4. Quick Check:

   Boolean type needed for ScanIndexForward [OK]

Hint: Use boolean false, not string 'false' for ScanIndexForward [OK]

Common Mistakes:

• Passing 'false' as a string instead of boolean
• Misunderstanding KeyConditionExpression syntax
• Ignoring data types in parameters

5. You want to retrieve the 5 most recent orders for customer cust123 from a DynamoDB table Orders with partition key CustomerId and sort key OrderDate. Which query will correctly return these orders in descending order by OrderDate?

hard

A. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :cid', ExpressionAttributeValues: { ':cid': 'cust123' }, ScanIndexForward: false, Limit: 5 })

B. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :cid', ExpressionAttributeValues: { ':cid': 'cust123' }, ScanIndexForward: false })

C. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :cid', ExpressionAttributeValues: { ':cid': 'cust123' }, Limit: 5 })

D. client.query({ TableName: 'Orders', KeyConditionExpression: 'CustomerId = :cid', ExpressionAttributeValues: { ':cid': 'cust123' }, ScanIndexForward: true, Limit: 5 })

Solution

1. Step 1: Set descending order for most recent first

   Use ScanIndexForward: false to get descending order by OrderDate.

2. Step 2: Limit results to 5

   Use Limit: 5 to get only the top 5 recent orders.

3. Final Answer:

   Query with ScanIndexForward false and Limit 5 -> Option A

4. Quick Check:

   Descending + Limit 5 = ScanIndexForward false + Limit 5 [OK]

Hint: Use ScanIndexForward false with Limit 5 for recent 5 items [OK]

Common Mistakes:

• Using ScanIndexForward true returns oldest first
• Omitting Limit returns all items
• Not combining descending order with limit