Getting data from previous row
create table t1 (
invoice_no int not null,
invoice_dt date not null,
invoice_amt decimal(13,2) not null,
last_dt date)
insert into t1 (invoice_no, invoice_dt, invoice_amt) values (4345, '2011-01-01', 44.33);
insert into t1 (invoice_no, invoice_dt, invoice_amt) values (3752, '2011-02-01', 44.33);
insert into t1 (invoice_no, invoice_dt, invoice_amt) values (5672, '2011-03-01', 44.33);
insert into t1 (invoice_no, invoice_dt, invoice_amt) values (1459, '2011-04-01', 44.33);
insert into t1 (invoice_no, invoice_dt, invoice_amt) values (2138, '2011-05-01', 44.33);
What I would like to achieve is to get the invoice_dt from the invoice that was prior to the row I am on. In this example, I would want '2011-04-01' on invoice_no 2138. Invoice 4345 would have nothing because it is the first row in the table.
The live data that I am working on has just over 20 million rows.
Any help is appreciated