snippetsqlModerate

How can I order rows returned by the RETURNING clause of a DML statement?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowscanorderreturnedthestatementreturningdmlhowclause

Problem

I am using this statement to get all the rows ordered by timestamp in ascending order

DELETE FROM @tableName
        WHERE id = ANY (
            SELECT id
            FROM @tableName
            WHERE source = :p1 AND target = :p2 @readCondition
            ORDER BY createddate
            LIMIT @limit
            FOR UPDATE SKIP LOCKED
        )
        RETURNING *;

And I am getting these results :

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.230886"

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.279604"

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.276191"
"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.202338"

As you can see they are not sorted by createdate. Is there a way to sort rows from the RETURNING clause?

Solution

You need to apply an ORDER BY to the result. To do that, put the DELETE statement into a common table expression. Then you can apply an ORDER BY when you select from it

with deleted as (
  DELETE FROM @tableName
  WHERE id = ANY (
        SELECT id
        FROM @tableName
        WHERE source = :p1 AND target = :p2 @readCondition
        ORDER BY createddate
        LIMIT @limit
        FOR UPDATE SKIP LOCKED)
  RETURNING *
)
select *
from deleted 
order by createddate;

Code Snippets

with deleted as (
  DELETE FROM @tableName
  WHERE id = ANY (
        SELECT id
        FROM @tableName
        WHERE source = :p1 AND target = :p2 @readCondition
        ORDER BY createddate
        LIMIT @limit
        FOR UPDATE SKIP LOCKED)
  RETURNING *
)
select *
from deleted 
order by createddate;

Context

StackExchange Database Administrators Q#254921, answer score: 10

Revisions (0)

No revisions yet.