asked on

in MySQL, how can I reference a row that is prior to another one based on the primary index?

I have a table, full_daily_data, that has as it's PRIMARY index the columns (symbol,trade_date).

I would like to be able to reference one row that immediately precedes another

Right now, I'm looking at something like this:

update fdd1
    set fdd1.Prior_Open = fdd2.Day_Open,
          fdd1.Prior_Close = fdd2.Day_Close
    from full_daily_data fdd1
    inner join full_daily_data fdd2
    on fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL 1 DAY);

Open in new window

But that only works if there are no gaps in the dates, and there are.

instead of

    on fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL 1 DAY);

Open in new window

Is there a way to join based on the sequence of the primary index being in date order?

Nem Schlecht

Well, you want the date from this query, don't you (note the DESC)

SELECT Trade_Date
FROM full_daily_data
ORDER BY Trade_DATE DESC
LIMIT 1
;

Open in new window

Would it be safe to do something like this:

SELECT @LATEST_TRADE_DATE:=Trade_Date
FROM full_daily_data
ORDER BY Trade_DATE DESC
LIMIT 1
;

update fdd1
    set fdd1.Prior_Open = fdd2.Day_Open,
          fdd1.Prior_Close = fdd2.Day_Close
    from full_daily_data fdd1
    inner join full_daily_data fdd2
    on fdd2.Trade_Date = @LATEST_TRADE_DATE
;

Open in new window

Looking at your scenario, I'm guessing this is pretty safe. Only only have to worry if the select statement grabs a date and somehow a new date is inserted in the microsecond before the UPDATE statement runs.

Nem Schlecht

It would be nice to combine these into one statement, like this, but MySQL doesn't let you do that. ;-(

update fdd1
    set fdd1.Prior_Open = fdd2.Day_Open,
          fdd1.Prior_Close = fdd2.Day_Close
    from full_daily_data fdd1
    inner join full_daily_data fdd2
    on fdd2.Trade_Date = (SELECT Trade_Date
          FROM full_daily_data
          ORDER BY Trade_DATE DESC
          LIMIT 1
    )
;

Open in new window

cashonly

ASKER

nemws1,

I was thinking about trying the select in the update... sorry to hear it doesn't work.

I'm not sure if your first solution will work. I was looking to do an update of all the data in one shot and it looks like the update would be based on a fixed trade date, not a relative one. It has to be based on the trade_date of the row that is receiving the "Prior_" data.

SOLUTION

Nem Schlecht

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

aside from the issue of aligning the correct dates, please notice that your joins require symbol in them... (it was an issue I attempted to point out in an earlier question but was too late)

update full_daily_data fdd1
inner join full_daily_data fdd2
on fdd1.symbol = fdd2.symbol
and fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL 1 DAY)
set
  fdd1.Prior_Open  = fdd2.Day_Open
, fdd1.Prior_High  = fdd2.Day_High
, fdd1.Prior_Low   = fdd2.Day_Low
, fdd1.Prior_Close = fdd2.Day_Close
;

Open in new window

see: http://sqlfiddle.com/#!9/cc5f0/1

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

cashonly, is this question finished now?