How can I return first and last values of a field in a group?

Posted on 2010-01-03
Last Modified: 2013-12-13
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:

Web Display::
ABC               -2.22
CBA               -3.33
XYZ               -8.88

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.


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.
Question by:chris_bc
    LVL 30

    Expert Comment

    by:Reza Rad
    could you bring a sample expected result?
    LVL 107

    Expert Comment

    by:Ray Paseur
    You might be able to select MIN() and MAX() on these fields, then use the selected values in the computations.  Or you could do a CREATE TEMPORARY TABLE ENGINE=MEMORY to isolate the date range.  If you do that you are working on a much smaller data set, and a multi-query operation will not be time-consuming at all.

    One other thought, instead of this:
    WHERE DATE >= '$fromdate' AND DATE <= '$todate'

    ... try something like this:
    WHERE DATE BETWEEN '$fromdate' AND '$todate'

    Doing that will cut the number of discreet steps for the date comparisons in half.

    And one final thought - DATE is a reserved word in MySQL.  You probably want to change that column name before it causes you trouble.

    Best regards, ~Ray

    Author Comment

    Not sure if MIN() and MAX() would work as I don't need the lowest and highest values in each group - just the first and last. Need some way to calculate % difference between the first Price and last Price for a given stock stock in a selected date range.

    I wonder if there's some way to incorporate into my existing query something like select price where date = min(date) and the max equivalent.(or some kind of nested select statement that would make my head spin).

    Thanks for the tip on DATE. Will change fieldname.

    Oh, and expected result (if user selects range Jan1 to Jan3) would be:

    TICKER          CHANGE       CHANGE(%)
    ABC               -2.22            -22.22%
    CBA               -3.33            -40.00%  
    XYZ               -8.88            -75.00%

    I can generate TICKER and CHANGE, but cant do the 3rd.
    LVL 107

    Expert Comment

    by:Ray Paseur
    I was thinking more about using MIN and MAX on the datetime fields to isolate the values on those dates.  But maybe the easiest way to do this would be to combine the PHP and MySQL, letting the query get you two values - one for the first date and one for the last date.  Then you can use PHP to iterate over the results set (not much iterating - only two fields involved) and compute the delta.

    Accepted Solution

    My apologies - the raw data set was slightly incorrect. The raw set is below.
    I have found a way to get first and last values in the group:

    a simple SELECT PRICE AS 'FIRST_PRICE''... will get the first price if the data is grouped by ticker.
    To get the last price, it is PRICE + SUM(CHANGE) AS 'LAST_PRICE' from alldata WHERE DATE BETWEEN '$fromdate' AND '$todate' GROUP BY TICKER. From there, I can just make a percentage calculation in the same statement.

    Seems to work ok.

    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             -5.55
    XYZ              01012009    XYZ Inc.             8.88              0.00
    XYZ              01022009    XYZ Inc.             5.55             -3.33
    XYZ              01032009    XYZ Inc.             2.22             -3.33
    LVL 9

    Expert Comment

    to make life easier, you can use change / 'first price' ( or 1 - change / 'first price' depends on what you want ) to get CHANGE(%), you don't need last prices

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now