First record combined with GROUP BY

Question

Let's say I got a table "values" which contains the fields id (int) name (varchar) value (float) timestamp (int)

Now I want to to calculate the highest lowest and first value (timestamp based) for each name on the entire values table.

Is this possible to be achieved in one single performant query? I stumbled upon the 'first_value' function, but that one doesn't seem to work. I tried the following query, using joins, but also without success.

SELECT 
    a.name, 
    b.value as open, 
    MIN(a.value) as low, 
    MAX(a.value) as high
FROM values a 
LEFT JOIN values b 
    ON a.name = b.name AND b.id = MIN(a.id) 
GROUP BY a.name;

Isn't there some sort of function which would make something similar as this possible?

SELECT 
    name, 
    FIRST_VALUE(value) as open, 
    MIN(value) as low, 
    MAX(value) as high
FROM values 
GROUP BY name 
ORDER BY timestamp ASC;

Example data

id  name    value   timestamp
1   USD     3       16540
2   EUR     5       16540
3   GBP     4       16540
4   EUR     2       16600
5   USD     4       16600
6   GBP     5       16600
7   USD     6       16660
8   EUR     7       16660
9   GBP     6       16660
10  USD     5       16720
11  EUR     5       16720
12  GBP     7       16720
13  EUR     8       16780
14  USD     7       16780
15  GBP     8       16780

Example output

name    open    low     high
USD     3       3       7
EUR     5       2       8
GBP     4       4       8

I'm using MySQL-client version: 5.6.39 A tie should not be possible, if it does, I don't care which value gets picked.

What you are looking to do is not very clear. Would you please provide sample data and expected results to clarify your question? — GMB, Oct 20 '19 at 21:44
Define "first" even when using ORDER BY the sort is not be fixed in cases where the values ties.. Also this ORDER BY is invalid with ANSI/ISO SQL GROUP BY rules.. like @GMB suggested we need to see example data and expected results.. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Oct 20 '19 at 21:47

GMB · Answer 1 · 2019-10-20T22:09:31.157

If you are running MySQL 8.0, this can be quite easily solved with window functions:

select name, value open, low, high
from (
    select
        name,
        value,
        min(value) over(partition by name) low,
        max(value) over(partition by name) high,
        row_number() over(partition by name order by timestamp) rn
    from mytable
) x
where rn = 1

Demo on DB Fiddle:

| name | open | low | high |
| ---- | ---- | --- | ---- |
| EUR  | 5    | 2   | 8    |
| GBP  | 4    | 4   | 8    |
| USD  | 3    | 3   | 7    |

In earlier versions, you could:

use a correlated subquery to filter on the first record for each name
join the table with an aggregate query that computes the min and max of each name

Query:

select 
    t.name,
    t.value open,
    t0.low,
    t0.high
from 
    mytable t
    inner join (
        select name, min(value) low, max(value) high from mytable group by name
    ) t0 on t0.name = t.name
where t.timestamp = (
    select min(t1.timestamp) from mytable t1 where t1.name = t.name
);

Demo on MySQL 5.6 DB Fiddle: same results as above

This could also be achieved using inline subqueries (which may actually perform better):

select 
    t.name,
    t.value open,
    (select min(value) from mytable t1 where t1.name = t.name) low,
    (select max(value) from mytable t1 where t1.name = t.name) high
from 
    mytable t
where timestamp = (
    select min(t1.timestamp) from mytable t1 where t1.name = t.name
)

Demo on MySQL 5.6 DB Fiddle

I'm using version 5.6.39. Perhaps I should consider simply updating my mysql. — Gilles Lesire, Oct 20 '19 at 22:09
@GillesLesire: yes, you should consider upgrading: MySQL 5.6 was released in 2013 and is out of support since 2018... Anyway, I updated my answer with solutions for this old version. — GMB, Oct 20 '19 at 22:14
@GillesLesire: does my (updated) answer properly responds to your question? — GMB, Oct 21 '19 at 19:44
Yes it does, however I found a more performant solution for the time being. I'm currently looking into upgrading my mysql version to version 8 and see if your solution offers better results. — Gilles Lesire, Oct 21 '19 at 19:56

James K. Lowden · Answer 2 · 2019-10-20T22:21:25.943

0

in one single performant query

Do it logically and let the DBMS worry about performance. If that isn't fast enough, check your indexes.

The value associated with the first timestamp requires a join. You can find the first timestamp easily enough. Getting a value from a row associated with a given row: that's what joins are for.

So, we have:

SELECT 
    name, 
    value as open, 
    v1.low
    v1.high
FROM values as v join (
    select name, 
    min(timestamp) as timestamp, 
    min(value) as low, 
    max(value) as high
    FROM values
    GROUP BY name 
) as v1
on v.name = v1.name and v.timestamp = v1.timestamp

edited Oct 20 '19 at 22:21

answered Oct 20 '19 at 22:05

James K. Lowden

7,574
1
16
31

I'm assuming "vi" is a typo and is supposed to be "v1"? – Gilles Lesire Oct 20 '19 at 22:08
Also the subquery needed a FROM statement to get it to work. – Gilles Lesire Oct 20 '19 at 22:15

score 0 · Answer 3 · answered Oct 21 '19 at 18:00

This solution seems to have the best performance.

SELECT 
name, 
CAST(SUBSTRING_INDEX(GROUP_CONCAT(CAST(value AS CHAR) ORDER BY TIMESTAMP ASC), ',', 1) AS DECIMAL(10, 6)) AS open, 
MIN(value) AS low, 
MAX(value) AS high
FROM mytable
GROUP BY name 
ORDER BY name ASC

First record combined with GROUP BY

3 Answers3