MySQL Forums
Forum List  »  Newbie

Re: Getting data from previous row
Posted by: Curtis Pastore
Date: September 20, 2011 12:20PM

Scott
I think you're going to have to join the table to another copy of itself on the invoice_no, but just toggle the date somehow in the WHERE clause

(20 million rows might be a bear but it also could not be...)
give this a whirl:

SELECT
 a.invoice_no, 
 a.invoice_dt AS 'Today',
 b.invoice_dt AS 'Yesterday',
 a.invoice_amt AS 'Amt_Today',
 b.invoice_amt AS 'Amt_Yesterday'
  FROM t1 AS a
  JOIN t1 AS b
    ON a.invoice_no = b.invoice_no
 WHERE b.invoice_dt = (SELECT max(invoice_dt) FROM t1 WHERE b.invoice_dt < a.invoice_dt LIMIT 1)

I'm not 100% sure about the syntax but thats the idea I'd start with.
Hope that helps!

Options: ReplyQuote


Subject
Written By
Posted
September 20, 2011 09:35AM
Re: Getting data from previous row
September 20, 2011 12:20PM
September 22, 2011 07:05PM
September 23, 2011 06:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.