CURSOR to find n-1

[awaywifye]

For one singe item in the database I could have multiple dates that it has appeared in the database(see below).

Record Barcode Date
1 123 3/25/2005
2 123 3/24/2004
3 123 11/12/2003
4 345 3/25/2005
5 345 3/24/2004
6 345 11/12/2003

I am trying to take this format and convert it to the following:

Record Barcode Date LastDate
1 123 3/25/2005 3/24/2004
2 123 3/24/2004 11/12/2003
3 123 11/12/2003 NULL
4 345 3/25/2005 3/24/2004
5 345 3/24/2004 11/12/2003
6 345 11/12/2003 NULL

So far I have managed to pull the max(date) and place it in every record for lastdate. I also managed to take the max(date) for each barcode and place it in the lastdate for the records with the same barcode. Any sort of help would be appreciated.

 

[vongrunt]

Cursor? Not:

select Record, BarCode, [Date], 
(	select max(B.[Date]) 
	from myTable B
	where A.BarCode=B.BarCode and B.[Date] < A.[Date]
) as LastDate
from myTable A
-- order by A.Record

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[awaywifye]

1) Thanks for the quick response!
2) DOH! It works. I got so hung up on the cursor idea that I didn't even consider this.