How to use select a greater order when datetime is the same?-CodePudding

I have a table:

CREATE TABLE TBL_A
(
    number_id int, 
    country varchar(50), 
    status varchar(50), 
    datetime date
);

INSERT INTO TBL_A
VALUES (121144, 'USA', 'CLICKED', '2021-10-09'),
       (121144, 'USA', 'CLAIMED', '2021-10-10'),
       (121144, 'USA', 'BOUGHT', '2021-10-11'),
       (121111, 'CAD', 'CLICKED', '2021-10-12'),
       (121111, 'CAD', 'CLAIMED', '2021-10-12'),
       (121111, 'CAD', 'BOUGHT', '2021-10-12'),
       (121133, 'AUS', 'CLICKED', '2021-10-14'),
       (121133, 'AUS', 'CLAIMED', '2021-10-14');

Based on the number_id and country, I want to grab the last status based on the datetime. The problem I am having is when the datetime has the same value. When this happens, I want the last status to follow this order:

> BOUGHT > CLAIMED > CLICKED

So if bought, claimed, and clicked have the same datetime, it should select BOUGHT. If there is no bought and claimed and clicked, have the datetime it should select CLAIMED.

I have this query to select the LAST status, but I need it to take into account the order when the datetime is the same:

SELECT    
    NUMBER_ID,
    COUNTRY,
    (array_agg(STATUS) WITHIN GROUP (ORDER BY DATETIME DESC)[0])::varchar AS LAST_STATUS
FROM
    TBL_A
GROUP BY 
    1, 2

CodePudding user response：

Using DECODE to add additiona column sort in case of a tie:

SELECT NUMBER_ID, COUNTRY
    ,(array_agg(STATUS) within group(order by DATETIME desc, 
                        DECODE(status, 'BOUGHT',1,'CLAIMED',2,'CLICKED',3, 99)
     )[0])::varchar as LAST_STATUS
from TBL_A
GROUP BY NUMBER_ID, COUNTRY;