TSQL Getting Info From Another Row in the Same Table?

Question

I am building a TSQL query to parse through a FTP log from FileZilla. I am trying to figure out if there is a way to get information from a line preceding the current one?

For example, I have parsed out the Following procedure: "STOR file.exe"

With the FileZilla is doesn't say if the STOR wass successful until the next line. So I want to check the next line and see if the STOR was successful or was unsuccessful?

Also people could try to STOR a files multiple times so I want to get the last version of its status.

Example Info from Log file:

   (000005) 4/10/2010 14:55:30 PM - ftp_login_name (IP Address)> STOR file.exe
   (000005) 4/10/2010 14:55:30 PM - ftp_login_name (IP Address)> 150 Opening data for transfer.
   (000005) 4/10/2010 14:55:30 PM - ftp_login_name (IP Address)> 226 Transfer OK

I want to add a column in my query that says that the STOR was successful or unsuccessful.

Thanks!

Answer 1

Assuming you have parsed these lines into actual columns, and you have SQL server 2005 or greater. You can use CROSS APPLY example query below (untested). I hope this helps.

select o.*, prev.*
from FTPLog o
cross apply 
 (
   select top 1 *
   from FTPLog P where P.LogDate < O.LogDate 
   order by LogDate DESC
 ) prev