Querying for greatest value of Range key on AWS DynamoDb

Question

What is the DynamoDB equivalent of

SELECT MAX(RANGE_KEY) FROM MYTABLE WHERE PRIMARYKEY = "value"

The best I can come up with is

from boto.dynamodb2.table import Table as awsTable

tb = awsTable("MYTABLE")
rs = list(tb.query_2(PRIMARYKEY__eq="value", reverse=True, limit=1))
MAXVALUE = rs[0][RANGE_KEY]

Is there a better way to do this?

Answer 1

That's the correct way.

Because the records matched by the Hash Key are sorted by the Range Key, getting the first one by the descendant order will give you the record with the maximum range key.

Query results are always sorted by the range key. If the data type of the range key is Number, the results are returned in numeric order; otherwise, the results are returned in order of ASCII character code values. By default, the sort order is ascending. To reverse the order use the ScanIndexForward parameter set to false.

Query and Scan Operations - Amazon DynamoDB : http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html

NOTE: Setting the reverse parameter to true via boto API is equivalent to setting ScanIndexForward to false via the native AWS API.

Answer 2

If someone looking how to do it with Java:

QuerySpec querySpec = new QuerySpec();
        querySpec.withKeyConditionExpression("PRIMARYKEY = :key")
                .withValueMap(new ValueMap()
                        .withString(":key", primaryKeyValue));
        querySpec.withScanIndexForward(true); 
        querySpec.withMaxResultSize(1);

Answer 3

In boto3 you can do it this way:

import boto3
from boto3.dynamodb.conditions import Key, Attr

kce = Key('table_id').eq(tableId) & Key('range').between(start, end)
output = table.query(KeyConditionExpression = kce, ScanIndexForward = False, Limit = 1) 

output contains the row associated with the Max value for the range between start and end. For the Min value change the ScanIndexForward to True