Jay <josip.2000@xxxxxxxxx> wrote: > Hi, > > I'm somewhat new to SQL so I need a bit of help with this problem. So > I have 2 tables: "selection" and "master", both have two columns in > each: "user_id" and "date". > > The "selection" contains one row for each "user_id" and depicts _one_ > "date" value for each user. > The "master" contains all "date" changes for each "user_id". I.e., > there are many dates for each "user_id". It is a history of previous > occurrences. > > Now, I want to add a 3rd column to the "selection" table that is the > "date" value from one step back for each "user_id". I.e., if the > "master" contains: > > User1 20010101 > User1 20000101 > User1 19990101 > User1 19970101 > > for User1, and the "selection" is > > User1 19990101 > > I want this to become: > > User1 20000101 19990101 > > How do I do this? A simple join wont do it since it is dependent on > what value "date" is for each user.. I think, you don't need a new column, because you can determine this value (assuming you have 8.4) test=*# select * from selection ; user_id | date ---------+---------- user1 | 20010101 user1 | 20000101 user1 | 19990101 user1 | 19970101 (4 Zeilen) Zeit: 0,255 ms test=*# select *, lag(date) over (order by date)from selection order by date desc; user_id | date | lag ---------+----------+---------- user1 | 20010101 | 20000101 user1 | 20000101 | 19990101 user1 | 19990101 | 19970101 user1 | 19970101 | (4 Zeilen) Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
