Search code examples
sqlrow

Grab Next Row in SQL

I am trying to grab a field from the next row of this database. I can use the logged on time to determine the logout time but I would like to also grab the next login time so I can determine the logout [logged off] time as well. Any suggestions? Thank you

|-LogonDate---|-PersonID-|-LoggedOnTime---|   
|5/30 12:00pm |100       |3600            | 
|5/30 01:00pm |100       |3430            |
|5/30 03:30pm |100       |3000            |

SELECT PersonID, LogonDate, LoggedOnTime
FROM Agent_Logout
Solution

  • It is possible with a analytic function. Don't know which DBMS you use but Oracle and I think also SQL Server can do it. I did it once in Oracle. There is an excellent side explaining it here I always go there when I need this analytic functions (Usually is rather RANK). Your will need a function called LEAD. I think it would work like this:

    SELECT PersonID, LogonDate, LoggedOnTime
       LEAD(LoggedOnTime, 1, 0) OVER (PARTITION BY PersonID ORDER BY LogonDate, LoggedOnTime)
FROM Agent_Logout

    Let me know if this does not work as you want (Because I haven't tested it). I will then try to go and make it work. But i am very confident ... if your database allows the syntax.