Wrapping the query in a derived table is the obvious way to solve this:
SELECT *
FROM
( SELECT DISTINCT ON (item_id)
item_id, data, timestamp, sort_order, ...
FROM [this table]
WHERE group_id = [some value]
AND timestamp < [some value]
ORDER BY item_id, timestamp DESC
) AS t
ORDER BY sort_order ;
If your original query is efficient and doesn't return too many rows, the additional sort will not add much cost to the query.
Besides DISTINCT ON
, there are a few other methods that are often more efficient in this type of "greatest-n-pre-group" queries. Most notable is using LATERAL
subqueries. See this excellent answers by Erwin: