How to ORDER BY a query in DynamoDB using PartiQL?

Question

There are some answers in some forums about this question where people are generally repeating the already confusing AWS documentation about DynamoDB, but I need an example to see how it actually works. I have this query :

SELECT id, message, created, FROM "messages"."sender_company_id-index" 
WHERE sender_company_id = 435634652 AND receiver_company_id = 69992528 
AND sender_user_id = 186 AND receiver_user_id = 201
ORDER BY id DESC

and it fails with this error message:

An error occurred during the execution of the command. ValidationException: Variable reference id in ORDER BY clause must be part of the primary key

created field is already indexed so what's wrong? Why does it require it to be "part of the primary key"? I would like to order by different fields in different cases so I cant have all of them set as primary key. I dont get it!

EDIT

I noticed some of the comments below state that ORDER BY is not a possibility using PartiQL. Also this SO link claims that. However, AWS official documentation states the opposite.

UPDATE:

I was able to make it work though not completely.

SELECT id, created, sender_company_id, receiver_company_id
       sender_user_name, sender_user_id, sender_company_name, receiver_company_name
       FROM "messages"."sender_company_id-created-index" 
       WHERE (sender_company_id = 435634652 OR receiver_company_id= 435634652)
       AND (sender_user_id = 186 OR receiver_user_id = 186) 
       ORDER BY created ASC, sender_company_id ASC

Error:

ValidationException: Must have at least one non-optional hash key condition in WHERE clause when using ORDER BY clause.

If I change the WHERE to this it works:

...WHERE sender_company_id = 435634652

Here is my indexes:

It is beyond my understanding what's going on here. I do have the indexes, I am using non-optional (key partitions) in the WHERE clause but it fails.

Answer 1

[EDIT: OP added index key details and a second PartiQL statement]

Error #1 is happening because you can only ORDER BY a field that is the sender_company_id-index index's Partition (=Hash) Key or Sort (=Range) Key^*. See the first block of unhappy examples marked ❌. Error #2 is happening because ORDER BY seems to be supported only for query operations (your second statement is a scan). This constraint makes sense, but I could not find it documented anywhere. See the second block of unhappy ❌ examples. But first, some working ORDER BY examples:

I need an example to see how it actually works.

my-table has a Partition Key PK and Sort Key SK. The table has one secondary index, named GSI1. The index uses the GSI1PK field as its Partition Key and its Sort Key is GSI1SK. Here's a sample record:

{
 "PK": "1x", "SK": "cc", "GSI1PK": "a", "GSI1SK": "b1", "Other": "foo", "Another": "bar"
}

These are valid PartiQL ORDER BY statements:

SELECT * FROM "my-table"        WHERE PK = '1x'                              ORDER BY SK     ASC
SELECT * FROM "my-table"."GSI1" WHERE GSI1PK = 'a'                           ORDER BY GSI1SK ASC
SELECT * FROM "my-table"."GSI1" WHERE GSI1PK IN ['a', 'b']                   ORDER BY GSI1PK DESC, GSI1SK ASC
SELECT * FROM "my-table"."GSI1" WHERE GSI1PK IN ['a', 'b']                   ORDER BY GSI1SK DESC, GSI1PK DESC
SELECT * FROM "my-table"."GSI1" WHERE GSI1PK IN ['a', 'b'] AND Other = 'foo' ORDER BY GSI1SK DESC, GSI1PK DESC

However, ordering by fields other than the Primary Keys of the index I'm querying on will fail:

-- ValidationException: Variable reference Other in ORDER BY clause must be part of the primary key
❌ SELECT * FROM "my-table"."GSI1" WHERE GSI1PK = 'a' ORDER BY Other ASC
-- ValidationException: Variable reference GSI1PK in ORDER BY clause must be part of the primary key
❌ SELECT * FROM "my-table" WHERE PK = 'a' ORDER BY GSI1PK ASC

ORDER BY apparently also requires a WHERE clause with a = or IN condition on the Partition Key. That is, ordering succeeds for query operations. Scan operations with ordering fail:

-- ValidationException: Must have WHERE clause in the statement when using ORDER BY clause.
❌ SELECT * FROM "my-table" ORDER BY SK ASC

-- ValidationException: Must have at least one non-optional hash key condition in WHERE clause when using ORDER BY clause.
❌ SELECT * FROM "my-table" WHERE begins_with(PK, 'b') ORDER BY SK ASC

the created field is already indexed so what's wrong?

DynamoDB secondary indexes are fundamentally different than their RDBMS cousins. A secondary index lets you query the data in the table using an alternate key.

Why does it require it to be "part of the primary key"? I would like to order by different fieldds

DynamoDB imposes design constraints to preserve its scalability superpowers.

---

* You can only order by the primary key elements of your statement's table[.index], as in the docs: SELECT ... FROM table[.index] ORDER BY key [DESC|ASC], where key is a "hash key or a sort key to use to order returned results."