MariaDB Find max in group of 3 columns

Question

I have a problem. In my database I have the following table:

| openTime      | market | coin | period |
|---------------|--------|------|--------|
| 1635768000000 | USDT   | ETH  | 1h     |
| 1635764400000 | USDT   | ETH  | 1h     |
| 1635760800000 | USDT   | ETH  | 1h     |
| 1635768000000 | USDT   | ETH  | 2h     |
| 1635760800000 | USDT   | ETH  | 2h     |
| 1635753600000 | USDT   | ETH  | 2h     |
| 1635768000000 | USDT   | BTC  | 1h     |
| 1635764400000 | USDT   | BTC  | 1h     |

Now what I want to query is the last openTime of every market-coin-period combination where the market and coin are given. In my case I want to find the last row of every USDT and ETH combination, so my result would be:

| openTime      | market | coin | period |
|---------------|--------|------|--------|
| 1635768000000 | USDT   | ETH  | 1h     |
| 1635768000000 | USDT   | ETH  | 2h     |

I already tried queries like this:

SELECT * FROM (SELECT * FROM Candlestick WHERE market = 'USDT' AND coin = 'ETH') a WHERE a.openTime IN (SELECT MAX(openTime) FROM (SELECT * FROM Candlestick) b GROUP BY b.period) GROUP BY a.coin, a.period;

But this returns me a weird result, where I get some kind of the latest rows, but not the last one for ETH. Can someone help me out?

PS: I am running: 10.3.31-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04

Answer 1

If you are using MySQL 8+, then use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY period ORDER BY openTime DESC) rn
    FROM yourTable
    WHERE market = 'USDT' AND coin = 'ETH'
)

SELECT openTime, market, coin, period
FROM cte
WHERE rn = 1;

Demo

Here is a way to do this using a join, in case your version of MariaDB does not support ROW_NUMBER:

SELECT t1.openTime, t1.market, t1.coin, t1.period
FROM yourTable t1
INNER JOIN
(
    SELECT period, MAX(openTime) AS maxOpenTime
    FROM yourTable
    WHERE market = 'USDT' AND coin = 'ETH'
    GROUP BY period
) t2
    ON t2.period = t1.period AND
       t1.openTime = t2.maxOpenTime AND
       t1.market = 'USDT' AND
       t1.coin = 'ETH';

Demo