Selecting first and last values in a group

Question

If you are using MySQL 8, the preferable solution would make use of the window functions FIRST_VALUE() and/or LAST_VALUE(), which are now available. Please have a look at Lukas Eder’s answer.

But if you’re using an older version of MySQL, those functions are not
supported. You have to simulate them using some kind of workarounds,
for example you could make use of the aggregated string function GROUP_CONCAT() that creates a set of all _open and _close values of the week ordered by _date for _open and by _date desc for _close, and extracting the first element of the set:

select
  min(_low),
  max(_high),
  avg(_volume),
  concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
  substring_index(group_concat(cast(_open as CHAR) order by _date), ',', 1 ) as first_open,
  substring_index(group_concat(cast(_close as CHAR) order by _date desc), ',', 1 ) as last_close
from
  mystockdata
group by
  myweek
order by
  myweek
;

Another solution would make use of subqueries with LIMIT 1 in the SELECT clause:

select
  min(_low),
  max(_high),
  avg(_volume),
  concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
  (
    select _open
    from mystockdata m
    where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
    order by _date
    LIMIT 1
  ) as first_open,
  (
    select _close
    from mystockdata m
    where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
    order by _date desc
    LIMIT 1
  ) as last_close
from
  mystockdata
group by
  myweek
order by
  myweek
;

Please note I added the LPAD() string function to myweek, to make the week number always two digits long, otherwise weeks won’t be ordered correctly.

Also be careful when using substring_index in conjunction with group_concat(): if one of the grouped strings contains a comma, the function might not return the expected result.

Leave a Comment Cancel reply