writing SQL queries to get neighbour rows

I have come across some problems writing queries for getting neighbour rows and have no idea what to do about it.

This is the scenario (it is not purely implemented using SQL at the moment, but rather a mix of SQL and Java implementation) :

There is an entity A which has 1000 entity B, each entity B has 1000 entity C, each entity C has 1000 entity D. We need to use the information stored in D. At first we have a query to get all entity Ds inside entity A. Then we implement some logic so that we can navigate from a particular D to the next D or previous D, but this logic is not written as SQL queries.

Question 1

If I want to implement the navigation logic as开发者_如何学编程 SQL queries, how can I go to the next D in my select query? Suppose the original query is as simple as

select * from D 
  inner join C where C.ID = D.FK
  inner join B where B.ID = C.FK

I am looking for a universal solution for both Oracle and SQL Server.

Question 2

How do I know what position is the current row (within the result of the above query). For example, if I want to implement some logic like "This is No. X of Y".

For those who are confused about what neighbor rows is, I mean the previous or next row of each row we get from the above query. The result query might be quite different with the one above, but you get the idea, I can specify the position of a particular D in the whole entity A using JAVA but I don't know how to do that using SQL.

---

If you are using Oracle, look into the LEAD and LAG keywords, they allow you to select with information from the rows before or after.