Re: Help me with this tricky join

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°