My table format is like this

I am trying to get results in this order

• Latest user submitted transaction on top
• Then order the actions that happened on each transaction in their created order

actions order will be pending, failed, completed.

I tried to group and order with RANK function like below, but no idea how to sort group by their first created date

select
    transaction_id,
    created_at,
    status,
    RANK() over (partition by transaction_id order by created_at) group_rank
from
    transactions;

CodePudding user response：

I would use ROW_NUMBER() and MAX() here:

WITH cte AS (
    SELECT *, MAX(created_at) OVER (PARTITION BY transaction_id) AS max_created_at,
              ROW_NUMBER() OVER (PARTITION BY transaction_id
                                 ORDER BY CASE status WHEN 'pending'   THEN 1
                                                      WHEN 'failed'    THEN 2
                                                      WHEN 'completed' THEN 3 END,
                                          created_at) rn
    FROM transactions
)

SELECT transaction_id, status, created_at, updated_at
FROM cte
ORDER BY max_created_at DESC, rn;

The ordering logic is that we first order each block of records belonging to the same transaction using the most recent created timestamp. Next, within each block we order by status. Finally, for two or more records having the same status, we again break that tie using the created timestamp.