ValidationException: Variable reference created_at in ORDER BY clause must be part of the primary key

Question

I created a table in DynamoDB. I want to get List of entity with username (local-secondary-index) and order by created_at.

When I tried in PartiQL this query.

SELECT * FROM "user-wallet-stage"."LSI_username" WHERE "username" = 'username' order by "created_at";

I got this error:

ValidationException: Variable reference created_at in ORDER BY clause must be part of the primary key

So as far as I research, I cannot use username as a local-secondary-index if I want to get list of entity with username, I should use Global secondary index. But I don't want to use GSI because of the costs.

Here is the table schema:

 Table:
  Type: AWS::DynamoDB::Table
  Properties:
    TableName: table-name
    ProvisionedThroughput:
      ReadCapacityUnits: 1
      WriteCapacityUnits: 1
    AttributeDefinitions:
      - AttributeName: id
        AttributeType: S
      - AttributeName: created_at
        AttributeType: N
      - AttributeName: username
        AttributeType: S
    KeySchema:
      - AttributeName: id
        KeyType: HASH
      - AttributeName: created_at
        KeyType: RANGE
    LocalSecondaryIndexes:
      - IndexName: LSI_username
        KeySchema:
          - AttributeName: id
            KeyType: HASH
          - AttributeName: username
            KeyType: RANGE
        Projection:
          NonKeyAttributes: []
          ProjectionType: "ALL"