How to get the maximum number in DynamoDB column?

Question

I have a DynamoDB table called URLArray that contains a list of URL's (myURL) and a unique video number (myNum).

I use AWS Amplify to query my table like so for example:

URLData = await API.graphql(graphqlOperation(getUrlArray, { id: "173883db-9ff1-4...."}));

Also myNum is a GSI, so i can also query the row using it, for example:

URLData = await API.graphql(graphqlOperation(getURLinfofromMyNum, { myNum: 5 }));

My question is, I would like to simply query this table to know what the maximum number of myNum is. So in this picture it'd return myNum = 12. How do i query my table to get this?

Answer 1

DynamoDb does not have the equivalent of the SQL expression select MAX(myNum), so you cannot do what you are asking with your table as-is.

A few suggestions:

• Record the highest value of myNum as you insert items into the table. For example, you could create an item with PK = "METADATA" and an attribute named maxMyNum. The maxMyNum attribute could be updated conditionally if you have a value that is higher than what is stored in DDB.

• You could build a secondary index with myNum as the sort key in a single partition. This would allow you to execute a query operation with ScanIndexForward set to false (descending order), and pick the first returned entry (the max value)

If you are generating an auto incrementing value in your application code, consider checking out the documentation regarding atomic counters.