Seleting previous/next record

[metaltree]

Hello,

I'd like to have a SELECT statement which also creates a calculated column based on the value of a field of the preceding record.

Example:

Column1 Column2 Calc1
1 North -
2 West North
3 South West
4 East South
5 Other East
...

I know there is a function for that in Oracle called LAG/LEAD (

http://www.adp-gmbh.ch/ora/sql/analytical/lag.html),

but it doesn't seem to exist in Access or PostgreSQL.

Any clue?

 

[PHV]

Something like this ?

SELECT A.Column1, A.Column2, B.Column2 Calc1
FROM yourTable A LEFT JOIN yourTable B ON A.Column1 = B.Column1 + 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[metaltree]

Works perfectly (I just had to remove the Calc1).
Many thanks PH!

Do you have any good reference (book or web tutorial) on that kind of trick?

SC

 

[smilewide]

Selecting previous/next record the way you described works fine... if you don't use any "order by" clause....

If we add more ctriteria, the result set comes up completely messed up...

any other way???