How to return the last value of each GROUP in a GROUP BY query?

Question

I need to get the last record (CLOSE field) of each GROUP of the GROUP BY query.

I have the following data structure

minute;symbol;timeframe;open;high;low;close;displayed

2021-02-22 00:00:00;EURUSD;M0;1.21103;1.21103;1.21103;1.21103;1
2021-02-22 00:00:00;EURUSD;M0;1.21103;1.21108;1.21103;1.21108;1

2021-02-22 00:02:00;EURUSD;M0;1.21127;1.21127;1.21127;1.21127;1
2021-02-22 00:02:00;EURUSD;M0;1.21127;1.21133;1.21127;1.21133;1

2021-02-22 00:03:00;EURUSD;M0;1.21125;1.21125;1.21125;1.21125;1
2021-02-22 00:03:00;EURUSD;M0;1.21125;1.21125;1.21109;1.21109;1

I tried the code below but it seems to return the latest CLOSE from the whole dataset

   SELECT minute, id, time, date, open, 
                (SELECT close FROM graphs GROUP BY minute ORDER BY minute DESC LIMIT 1) AS close, 
            MIN(low) AS low, 
            MAX(high) AS high
            FROM graphs
            WHERE 
                symbol = 'EURUSD' AND 
                date >= '1420070400' AND 
                date <= '1640995199' AND 
                timeframe = 'M0' AND 
                showed = '1'
            GROUP BY minute

Any suggestion??

https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql — Anurat Chapanond, Mar 15 '21 at 03:17

score 0 · Answer 1 · answered Mar 15 '21 at 01:27

Use row_number(). I'm not sure what the exact logic is, because the query references time which is not in the sample data, but something like:

select g.*
from (select g.*,
             row_number() over (partition by minute , symbol order by time desc) as seqnum
      from graphs g
      where g.symbol = 'EURUSD' AND 
            g.date >= '1420070400' AND 
            g.date <= '1640995199' AND 
            g.timeframe = 'M0' AND 
            g.showed = '1'
     ) g
where seqnu = 1;

How to return the last value of each GROUP in a GROUP BY query?

1 Answers1