-1

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

A. Vreeswijk
  • 822
  • 1
  • 19
  • 57

1 Answers1

2

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;

screen capture from demo link below

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • @A.Vreeswijk This code must work in your MaraDB version. https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=9fefef940d88e226d1aa3c8c8f929759 – Akina Nov 11 '21 at 10:52
  • @Akina `ROW_NUMBER` is only supported on MariaDB version `10.2` or later. It's conceivable that the OP is using an older version. – Tim Biegeleisen Nov 11 '21 at 10:56
  • OP tells that he uses **10.3.31**-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04 – Akina Nov 11 '21 at 10:57
  • Yeah, both code pieces are working! However, the first one takes way too long for my data. I have arround 6 million records and the query takes about 1 minute. The second one is really fast! – A. Vreeswijk Nov 11 '21 at 10:58
  • Your performance observations are truly bizarre, because `ROW_NUMBER` has been internally optimized to be really fast. I have no explanation for why the second version is faster. – Tim Biegeleisen Nov 11 '21 at 10:58