Query result ordering (ascending, descending) in DynamoDB - Mini Project: Build & Apply

Query result ordering (ascending, descending)

📖 Scenario: You are managing a small online bookstore database using DynamoDB. You want to retrieve books sorted by their publication year, either from oldest to newest or newest to oldest.

🎯 Goal: Build a DynamoDB query that fetches books ordered by their publication_year in ascending or descending order.

📋 What You'll Learn

Create a DynamoDB table named Books with author as the partition key and publication_year as the sort key.

Set a variable author_name to query books by a specific author.

Write a query to get books by author_name ordered by publication_year ascending.

Modify the query to order the results by publication_year descending.

💡 Why This Matters

🌍 Real World

Ordering query results is common when you want to show data sorted by dates, prices, or other criteria in apps like bookstores, blogs, or event listings.

💼 Career

Understanding how to order query results in DynamoDB is important for backend developers and database administrators working with AWS services.

1

Create the DynamoDB table structure

Create a DynamoDB table named Books with author as the partition key and publication_year as the sort key.

DynamoDB

# Define the Books table with author as partition key and publication_year as sort key
# Your code here

Hint

Use KeySchema to define partition and sort keys. author is HASH key, publication_year is RANGE key.

2

Set the author name to query

Create a variable called author_name and set it to the string 'J.K. Rowling' to specify the author for the query.

DynamoDB

Books = {
    'TableName': 'Books',
    'KeySchema': [
        {'AttributeName': 'author', 'KeyType': 'HASH'},
        {'AttributeName': 'publication_year', 'KeyType': 'RANGE'}
    ],
    'AttributeDefinitions': [
        {'AttributeName': 'author', 'AttributeType': 'S'},
        {'AttributeName': 'publication_year', 'AttributeType': 'N'}
    ],
    'ProvisionedThroughput': {
        'ReadCapacityUnits': 5,
        'WriteCapacityUnits': 5
    }
}

# Set the author_name variable to 'J.K. Rowling'
# Your code here

Hint

Assign the exact string 'J.K. Rowling' to the variable author_name.

3

Write a query to get books ordered ascending by publication year

Write a DynamoDB query named query_ascending that queries the Books table for items where author equals author_name, and orders the results by publication_year in ascending order (oldest first). Use KeyConditionExpression and set ScanIndexForward to True.

DynamoDB

Books = {
    'TableName': 'Books',
    'KeySchema': [
        {'AttributeName': 'author', 'KeyType': 'HASH'},
        {'AttributeName': 'publication_year', 'KeyType': 'RANGE'}
    ],
    'AttributeDefinitions': [
        {'AttributeName': 'author', 'AttributeType': 'S'},
        {'AttributeName': 'publication_year', 'AttributeType': 'N'}
    ],
    'ProvisionedThroughput': {
        'ReadCapacityUnits': 5,
        'WriteCapacityUnits': 5
    }
}

author_name = 'J.K. Rowling'

# Write query_ascending to get books by author_name ordered by publication_year ascending
# Your code here

Hint

Use ScanIndexForward: True to order ascending by the sort key.

4

Modify the query to order descending by publication year

Create a new query named query_descending that is the same as query_ascending but orders the results by publication_year in descending order (newest first). Set ScanIndexForward to False.

DynamoDB

Books = {
    'TableName': 'Books',
    'KeySchema': [
        {'AttributeName': 'author', 'KeyType': 'HASH'},
        {'AttributeName': 'publication_year', 'KeyType': 'RANGE'}
    ],
    'AttributeDefinitions': [
        {'AttributeName': 'author', 'AttributeType': 'S'},
        {'AttributeName': 'publication_year', 'AttributeType': 'N'}
    ],
    'ProvisionedThroughput': {
        'ReadCapacityUnits': 5,
        'WriteCapacityUnits': 5
    }
}

author_name = 'J.K. Rowling'

query_ascending = {
    'TableName': 'Books',
    'KeyConditionExpression': 'author = :author_val',
    'ExpressionAttributeValues': {':author_val': {'S': author_name}},
    'ScanIndexForward': True
}

# Create query_descending like query_ascending but with ScanIndexForward set to False
# Your code here

Hint

Set ScanIndexForward to False to order descending by the sort key.

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