Re: Help me with this tricky join
От | Andreas Kretschmer |
---|---|
Тема | Re: Help me with this tricky join |
Дата | |
Msg-id | 20100320071303.GA24839@tux обсуждение исходный текст |
Ответ на | Help me with this tricky join (Jay <josip.2000@gmail.com>) |
Список | pgsql-general |
Jay <josip.2000@gmail.com> 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°
В списке pgsql-general по дате отправления: