Query result ordering (ascending, descending) in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the order of results returned by this DynamoDB query?

Consider a DynamoDB table with a partition key UserID and a sort key Timestamp. You run this query to get all items for UserID = 'user123':

QueryRequest request = new QueryRequest()
    .withTableName("UserActivity")
    .withKeyConditionExpression("UserID = :uid")
    .withExpressionAttributeValues(Map.of(":uid", new AttributeValue().withS("user123")))
    .withScanIndexForward(true);

What order will the items be returned in?

A Items are returned in ascending order by the sort key (Timestamp).

B Items are returned in descending order by the sort key (Timestamp).

C Items are returned in random order.

D Items are returned in ascending order by the partition key (UserID).

💡 Hint

Look at the withScanIndexForward(true) parameter.

Setting ScanIndexForward to true returns results in ascending order by the sort key.

❓ query_result

intermediate

How to get descending order results from a DynamoDB query?

You want to get the latest activity first for a user in a DynamoDB table with UserID as partition key and Timestamp as sort key. Which parameter should you set in the query?

A Set <code>ScanIndexForward</code> to <code>true</code>.

B Set <code>ReturnConsumedCapacity</code> to <code>INDEXES</code>.

C Set <code>ConsistentRead</code> to <code>true</code>.

D Set <code>ScanIndexForward</code> to <code>false</code>.

💡 Hint

Think about reversing the order of the sort key.

Setting ScanIndexForward to false returns results in descending order by the sort key.

🧠 Conceptual

advanced

Why can't you order DynamoDB query results by attributes other than the sort key?

DynamoDB queries allow ordering results by the sort key only. Why is it not possible to order by other attributes?

A Because DynamoDB stores data sorted only by partition key and sort key, so ordering by other attributes would require scanning all data.

B Because DynamoDB automatically orders by all attributes internally, so specifying others is redundant.

C Because DynamoDB does not support any ordering of query results.

D Because ordering by other attributes is only possible with a Scan operation.

💡 Hint

Think about how DynamoDB stores and indexes data physically.

DynamoDB stores items sorted by partition key and sort key only. Ordering by other attributes would require scanning all items, which is inefficient and not supported in Query.

📝 Syntax

advanced

Identify the syntax error in this DynamoDB query for descending order

Which option contains a syntax error in setting the query to return results in descending order?

QueryRequest request = new QueryRequest()
    .withTableName("UserActivity")
    .withKeyConditionExpression("UserID = :uid")
    .withExpressionAttributeValues(Map.of(":uid", new AttributeValue().withS("user123")))
    .withScanIndexForward(???);

A withScanIndexForward(Boolean.FALSE)

B withScanIndexForward(false)

C withScanIndexForward("false")

D withScanIndexForward(0)

💡 Hint

Check the expected data type for withScanIndexForward.

The withScanIndexForward method expects a boolean value, not a string.

❓ optimization

expert

How to efficiently get the last 5 items in descending order from a DynamoDB table?

You want to get the 5 most recent activities for a user from a DynamoDB table with UserID as partition key and Timestamp as sort key. Which approach is best?

A Query with <code>ScanIndexForward(true)</code> and <code>Limit(5)</code> to get first 5 items in ascending order.

B Query with <code>ScanIndexForward(false)</code> and <code>Limit(5)</code> to get last 5 items in descending order.

C Use a Global Secondary Index on Timestamp and scan it to get last 5 items.

D Scan the whole table and sort results in application code to get last 5 items.

💡 Hint

Think about how to limit data read and get results in desired order.

Querying with ScanIndexForward set to false returns items in descending order by sort key. Using Limit(5) restricts results to 5 items, making it efficient.

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