Majlena Majlena - 5 months ago 11
SQL Question

How to select the first two row of each group and count difference between them in one column?

I have table like that

ID_WE ID_B ID_WO RDATA RSIZE
11111 22 1 1998-10-01 14
11111 22 2 1998-09-30 17
11111 23 1 1998-10-01 23
11112 22 1 1998-09-30 14


ID_WE
,
ID_B
and
ID_WO
together are primary key. For every combination id-we, id_b there are few
ID_WO
. Every
ID_WO
have a lot of reads, information about reads are in
RDATA
and
RSIZE


I need to fetch table like that

ID_WE ID_B ID_WO DAYS DIF
11111 22 1 1 0


DIF
is difference in
RSIZE
between two last reads for given
ID_WO
,
DAYS
is how many days have passed between two last reads

It probably need some group by and maybe max(rdata) to get the that to count difference in days and size. I'm really lost how to get result like that. I will appreciate any tips how to get required results.

Answer

Thank you all for comments and answers, that have got me on right track of thinking. I finally have figure it out. With help from this page on Baron Schwartz's Blog (it's usefull resource if someone need to select the first/least/max row per group in SQL).

I get the bigest and seconde bigest RDATA and add to data coresponding RSIZE in query like that, biggest(FOO):

select oo.id_wej,oo.id_ob,oo.id_wo, oo.odata,oo.od
from (select id_wej,id_ob,id_wo,odata from odczyty
where odata = (select max(odata) from odczyty o where o.id_wej=odczyty.id_wej and o.id_ob=odczyty.id_ob and o.id_wo=odczyty.id_wo)) as x
inner join odczyty oo on oo.id_wej=x.id_wej and oo.id_ob=x.id_ob and oo.id_wo=x.id_wo and oo.odata=x.odata
order by id_wej,id_ob,id_wo)as ok
on od.id_wej=ok.id_wej and od.id_ob=ok.id_ob and od.id_wo=ok.id_wo

and second biggest(BAR):

select o.id_we,o.id_b,o.id_wo, o.rdata,o.rsize
from (select id_we,id_b,id_wo,rdata from odczyty
where rdata =(select max(rdata)
from odczyty o2
where o2.id_we=odczyty.id_we and o2.id_b=odczyty.id_b
and o2.id_wo=odczyty.id_wo and
rdata <(select max(rdata) from odczyty o3
where o3.id_we=o2.id_we and
o3.id_b=o2.id_b and o3.id_wo=o2.id_wo))) as x
inner join odczyty o on o.id_we=x.id_we and o.id_b=x.id_b and o.id_wo=x.id_wo and o.rdata=x.rdata

Using this two queries i made this :

   select od.id_wej,od.id_ob,od.id_wo, ok.odata-od.odata as days,ok.od-od.od as dif
from(BAR)as od  inner join
(FOO)as ok
on od.id_wej=ok.id_wej and od.id_ob=ok.id_ob and od.id_wo=ok.id_wo
order by id_wej, id_ob, id_wo

I replaced inserted queries with FOO and BAR for readability.

Comments