hope can help.
i have 2 mysql select statements each work, need combine them.
first:
select $shares.*, $shares_company.name name, sum($shares.number) totalnumber, max(date) lasttransdate, $shares.timestamp lastupdated $shares inner join $shares_company $shares_company.id = $shares.company_id group $shares.company_id
second:
select $shares.price lastprice $shares ($shares.company_id,$shares.date) in (select $shares.company_id, max($shares.date) $shares group $shares.company_id)
i tried union
, union all
, , sorts of other combinations.
i stuck!
update:
well, that's how far got. returns correct info except need sum of 'number' column , don't know insert query. query insert is:
select sum($shares.number) totalnumber $shares group $shares.company_id
the query have is:
select $shares.price lastprice, $shares.date lasttransdate, $shares.company_id, $shares.id, $shares.timestamp lastupdated, $shares_company.name $shares inner join $shares_company on $shares_company.id = $shares.company_id ($shares.company_id,$shares.date) in (select $shares.company_id, max($shares.date) $shares group $shares.company_id)
thanks help.
if understand correctly, want lastprice result within first query. in case, i'd try this:
select $shares.*, $shares_company.name name, sum($shares.number) totalnumber, max(date) lasttransdate, $shares.timestamp lastupdated $shares, ( select $s.price $shares $s $s.company_id = $shares.company_id order $s.date desc limit 1 ) lastprice inner join $shares_company on $shares_company.id = $shares.company_id group $shares.company_id
(note: untested, reasoning should clear: create inner query select last share price given row, return column).
Comments
Post a Comment