36

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?

Vishal
  • 2,097
  • 6
  • 27
  • 45

3 Answers3

22

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.

b-s-d
  • 4,953
  • 2
  • 25
  • 31
  • When I query it is returning, all the records with the specified hash key, reverse sorted by range key. Is it possible to get **that only record** with the greatest range key? – Thiyagu Mar 14 '16 at 08:51
  • Yes , set the "limit" parameter to 1: "The DynamoDB Query and Scan APIs allow a Limit value to restrict the size of the results." – b-s-d Mar 14 '16 at 13:01
  • I posted a question [here](http://stackoverflow.com/questions/35983291/dynamodb-querying-for-the-greatest-value-of-a-range-key/35984890#35984890). In fact, that did not work. I had to use queryPage of DynamoDBMapper – Thiyagu Mar 14 '16 at 16:04
  • What would be the equivalent syntax in boto3? If all my hashkeys are the same the query returns the entire table, but sorted. Does not seem to be efficient. Is this still the way to do it it boto3? – Nazar Mar 21 '18 at 23:19
13

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);
user3485142
  • 151
  • 1
  • 6
  • 1
    Note that to get the greatest range key you need to pass false to withScanIndexForward. But overall your code fragment works, thanks. – Arthur Oct 26 '17 at 19:51
  • Hi I have a similar use case, I have 1 doubt, if you are querying by primary key, other filters aren't useful then? – Dev1ce Feb 19 '18 at 11:12
  • I guess primary key is a bit confusing, it is a hash key, which is not unique. The primary key is made of hash and rangekey combination – user3485142 Feb 19 '18 at 21:08
7

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

abedfar
  • 1,989
  • 24
  • 21