How can I return first and last values of a field in a group?
Posted on 2010-01-03
I have raw data (1 million rows) that is structured as follows:
TICKER DATE COMPANY PRICE CHANGE
ABC 01012009 ABC Inc. 9.99 0.00
ABC 01022009 ABC Inc. 8.88 -1.11
ABC 01032009 ABC Inc. 7.77 -1.11
CBA 01012009 CBA Inc. 5.55 0.00
CBA 01022009 CBA Inc. 8.88 3.33
CBA 01032009 CBA Inc. 3.33 -6.66
XYZ 01012009 XYZ Inc. 8.88 0.00
XYZ 01022009 XYZ Inc. 5.55 -5.55
XYZ 01032009 XYZ Inc. 2.22 -3.33
On a web page, a user selects a date range so he can see the best performing tickers (best at the top, worst at bottom), grouped by ticker. In this case he selects from: 01012009 to 01032009:
I cant work out how to get a 3rd column showing the percent change of PRICE for the selected date range. (eg from 01012009 to 01032009)
In PHP I have a query that is written as follows.
$query = "SELECT TICKER, DATE, COMPANY, PRICE, SUM(MONETARY_CHANGE) AS 'CHANGE' FROM alldata WHERE DATE >= '$fromdate' AND DATE <= '$todate' GROUP BY TICKER ORDER BY SUM(MONETARY_CHANGE) DESC";
It works fine, but I cannot work out how to get the fist and last PRICE values from each group so I can make a calculation. Not sure if it is best to capture this in SQL or when my php array starts to output later in the page.