Get Next Row Value in Current Row in Query

[waqas]

Dear Experts,



i want to get next row value in the current row. how to write a query please assist me.

[waqas]

dear experts,


how to write a query? i want if login is empty then it should take next max logout in NextTime column for current record.
if login is not empty then it should take max login to NextTime column as i mentioned in below table

LOGIN	LOGOUT	NextTime
	03-Jan-17 06:57:22	03-Jan-17 07:10:22
	03-Jan-17 07:10:22	03-Jan-17 07:11:55
	03-Jan-17 07:11:55	03-Jan-17 07:38:00
	03-Jan-17 07:38:00	03-Jan-17 07:55:47
	03-Jan-17 07:55:47	03-Jan-17 07:57:01
	03-Jan-17 07:57:01	03-Jan-17 08:18:43
	03-Jan-17 08:18:43	03-Jan-17 08:59:38
	03-Jan-17 08:59:38	03-Jan-17 09:19:56
	03-Jan-17 09:19:56	03-Jan-17 09:27:40
03-Jan-17 09:27:40	03-Jan-17 09:35:16
03-Jan-17 09:29:16	03-Jan-17 09:36:06
	03-Jan-17 09:44:09
03-Jan-17 09:59:18	03-Jan-17 10:10:08

[/QUOTE][/QUOTE][/QUOTE]

[CJ_London]

there is no concept in databases for next, previous, first, last so you will need a unique value of some sort that the data is sorted on that can be used to identify the next row - might be an autonumber, might be a date, might be something else.

so as an example assuming your data looks something like this, sorted by ID

myTable
ID..Field1
1...A
2...B
3...C
etc

your query would be like this

SELECT *, (SELECT TOP 1 Field1 FROM myTable T WHERE ID>myTable.ID ORDER BY ID) AS NextValue
FROM myTable

and will return

ID..Field1..NextValue
1...A.........B
2...B.........C
3...C.........Null

[CJ_London]

using you example

SELECT *, (SELECT TOP 1 nz(Login,Logout) FROM myTable T WHERE Logout>myTable.Logout ORDER BY Logout) AS NextTime
FROM myTable

I would also add that I suspect your table design can be improved (normalised) to make things simpler - your table should be structured

LogDate..Login
3/1/17....out
4/1/17....out
5/1/17....in

If what you have provided is the way the data is presented to you - write two append queries to a normalised table, one for login and one for logout.

[waqas]

but image table is a query result NextTime column is coming from subquery how i get if login column is not empty this time should be appear in nexttime column against logout record.

[CJ_London]

suggest display your tables then

[waqas]

First two columns are my table. Take these two columns as table and in query create third column as in picture.

[CJ_London]

In that case I do not understand your problem - the query I suggested in post#4 provides the result required in your original post - subject to you correcting for your real table name

[CJ_London]

Sorry, just noticed a typo in post#4, now corrected