Skip to main content
1 of 3
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

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:

ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305