Getting row with highest value - if multiple rows take another condition

Question

I have a table where I store "bids". I need to get the row with the highest amount per group (PlayerID). But if there are multiple rows due to a "bid" at the same moment I need to get the earliest one (BidDate).

My table and test date looks like this (DB-Fiddle):

CREATE TABLE bid(
    BidID integer PRIMARY KEY,
    PlayerID integer,
    Amount integer,
    BidDate text,
    User integer
);
INSERT INTO bid VALUES(1,1,1500000,NULL,0);
INSERT INTO bid VALUES(2,2,5875000,0,0);
INSERT INTO bid VALUES(3,1,1500000,'1625513541.2904',505414867524517888);
INSERT INTO bid VALUES(4,1,1500000,'1625513541.33661',840702168863735889);

I tried different queries already. But If I get the max(amount) I am still left with the date. You will see also Amounts of Null or 0 which I used for testing Min(NULLIF(BidDate)) which did not help.

The result I am expecting (wanted to achieve) would be in this case:

 BidID | PlayerID |  Amount  |    BidDate      |        User        |
 ------|----------|----------|-----------------|--------------------|
    3  |     1    |  1500000 | 1625513541.2904 | 505414867524517888 |
    2  |     2    |  5875000 |     0 or Empty  |         0          |

Any suggestion or help is appreciated.

Answer 1

The tricky part for your requirement is that in the column BidDate you have nulls or 0 (although you have defined the column as text).

Use ROW_NUMBER() window function with the proper ORDER BY clause that takes into account these irregularities:

SELECT BidID, PlayerID, Amount, BidDate, User
FROM (
  SELECT *, ROW_NUMBER() OVER (
              PARTITION BY PlayerID 
              ORDER BY Amount DESC, COALESCE(BidDate, 0) = 0, BidDate
            ) rn
  FROM bid
)  
WHERE rn = 1

See the demo.