CODEWITHSUNDEEP
mysqlsql
hongchangfirst
hongchangfirst

Reputation: 315

Get max record and min record together via MySQL

Having a dataset like this:

id inv value
1  1   10
1  2   12
1  3   4
2  1   20
2  2   2

How to write a SQL to get the dataset, for each id, use the min inv record minus max inv record's value data, the dataset looks like below:

id delta_value min_inv max_inv
1    6           1       3
2    18          1       2

Please notice that max inv's value doesn't necessarily mean max value.

Upvotes: 0

Views: 60

Answers (4)

ysth
ysth

Reputation: 98398

select id, delta_value, min(inv) min_inv, max(inv) max_inv
from (
    select
        id,
        first_value(value) over (partition by id order by inv)
        - first_value(value) over (partition by id order by inv desc) delta_value,
        inv
    from foo
) foo group by id, delta_value;

Upvotes: 1

Rajat
Rajat

Reputation: 5803

Assuming your MySQL version supports window functions, you could pick the max and min value per id, based on matching max and min inv per id. This works especially if you have multiple values per id per inv

select id, 
       max(value)-min(value) as delta_value,
       max(inv) as max_inv, 
       min(inv) as min_inv
from (select id, 
             inv, 
             case when inv = max(inv) over (partition by id) 
                    or inv = min(inv) over (partition by id) then value  end as value
           from your_table) t
group by id;

DEMO

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269863

If inv always starts from 1, then conditional aggregation comes to mind:

select id,
       sum(case when inv = max_inv then value
                when inv = 1 then - value
           end),
       min(inv), max(inv)
from (select t.*,
             max(inv) over (partition by id) as max_inv
      from t
     ) t
group by id;

You can even do this without subqueries (although the performance is probably no better):

select distinct id,
       (first_value(value) over (partition by id order by inv desc) -
        first_value(value) over (partition by id order by inv asc)
       ) as diff,
       min(inv) over (partition by id),
       max(inv) over (partition by id)
        
from t;

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23361

You will need to create a subset of your set to get desired values, this is Mysql < 8 compatible query. With MySql 8+ you can see answer provided by @ysth

select imm.id, 
       tmin.value-tmax.value as delta_value,
       imm.min_inv,
       imm.max_inv
  from (select id, 
               min(inv) min_inv, 
               max(inv) max_inv 
          from yourTable 
         group by id) imm 
        inner join yourTable tmin on tmin.id = imm.id
                                    and tmin.inv = imm.min_inv
        inner join yourTable tmax on tmax.id = imm.id
                                    and tmax.inv = imm.max_inv

See it working here: http://sqlfiddle.com/#!9/88a4d6/2

Upvotes: 0

Related Questions