Thread: Help me with this tricky join

Help me with this tricky join

From
Jay
Date:
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..
Moreover, I know for a fact that the "date" in "selection" is the
second largest value -> I want to add the 3rd largest value from
"master" for each user.

BR,
Jay

Re: Help me with this tricky join

From
Jay
Date:
CORRECTION:

The original "selection" should be:

User1  20000101

NOT

User1  19990101

Re: Help me with this tricky join

From
Andreas Kretschmer
Date:
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°

Re: Help me with this tricky join

From
Jay
Date:
Thanks!
But, since the master can contain many users (user2, user3, and so on)
I suppose this won't be a proper solution?
Sorry if I was a bit unclear in my description.

I.e., the master is of the form:

user_id date
User1 20010101
User1 20000101
User1 19990101
User1 19970101
User2 ...
...

Btw, I'm using Postgre version 8.2 and I cannot use subqueries do the
GP software.


Re: Help me with this tricky join

From
"A. Kretschmer"
Date:
In response to Jay :
> Thanks!
> But, since the master can contain many users (user2, user3, and so on)
> I suppose this won't be a proper solution?
> Sorry if I was a bit unclear in my description.
>
> I.e., the master is of the form:
>
> user_id date
> User1 20010101
> User1 20000101
> User1 19990101
> User1 19970101
> User2 ...
> ...

That's not the problem ...

>
> Btw, I'm using Postgre version 8.2 and I cannot use subqueries do the

but this.
lag() over () and similar windowing functions new since 8.4.



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99