Re: Help me with this tricky join

Jay <josip(dot)2000(at)gmail(dot)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°